Difference in Comma separated values

[brindlogcool]

Is it possible to get the difference in the comma separated values. For example if i have the table as

Key, ct

A,10,20,30,40  

B, 20,50,70

c, 1,2

so i am looking for

Key, ct

A,  10,10,10    ------> Got the values in ct by 20-10,30-20,40-30

B,   30,20       ------> Got the values in ct by 20-50,50-70

c,   1              ------> Got the values in ct by 2-1

so on..... it is calculating the difference in the dynamic comma separated values without splitting into multiple columns. but it would be consistent as per the above example.

[petter]

LOAD 
  Key,
  c2-c1 AS c1,
  c3-c2 AS c2,
  c4-c3 AS c3
INLINE [
Key, c1,c2,c3,c4
A,10,20,30,40  
B, 20,50,70
C, 1,2
];

[brindlogcool]

Thanks Petter,

It won't work because the no of comma separated values is dynamic and it is not predefined count.

[petter]

What is the upper limit of separated values then? It is not unlimited I would guess?

[Clever_Anjos]

Please check if fits

LOAD
  Key,
  x,
  Evaluate(x) as val
where Len(x) > 1;
LOAD
  Key,
  ct,
  if(Key = Previous(Key), ct&'-'&Previous(ct)) as  x;
LOAD Key,SubField(ct,',') as ct INLINE [
    Key, ct
    A, "10,20,30,40"
    B, "20,50,70"
    c, "1,2"
];

[brindlogcool]

It works but again i need to one more step for concat the values.

So i understand the steps are

(a) Convert the comma separated values into rows

(b) Previous to load the value which needs to subtracted and create an expression

(c) Evaluate to calculate the expression...

[Clever_Anjos]

Maybe this?

LOAD
  Key,
  Concat(val,',') as val
Group by Key;
LOAD
  Key,
  Evaluate(x) as val
where Len(x) > 1;
LOAD
  Key,
  if(Key = Previous(Key), ct&'-'&Previous(ct)) as  x;
LOAD Key,SubField(ct,',') as ct INLINE [
    Key, ct
    A, "10,20,30,40"
    B, "20,50,70"
    c, "1,2"
];

[brindlogcool]

Thanks Again Anjos,

it works i did the additional step what you had mentioned over here.

what i thought was is there any straightforward  step without going through multiple steps.

[Peter_Cammaert]

This alternative reads CSV files with varying length records. And you even don't have to double-quote anything. Also you could STORE the output in another CSV file.

To get a source file, put your example data in a file called Dynamic.csv and place it in the same directory as your document.

RawData:
LOAD AutoNumber(RowNo(), RecNo()) AS SeqNr,
     SubField(Line, ',', 1) AS Key,
     SubField(Mid(Line, Index(Line, ',', 1)+1), ',') AS Data;
LOAD [@1:n] AS Line
FROM [.\Dynamic.csv](fix, codepage is 1252, header is 1 lines);


MergedData:
LOAD Key, Data,
     IF (SeqNr > 1, Data - peek('Data')) AS Diff
RESIDENT RawData;


DROP Table RawData;


FinalTable:
LOAD Key & ',' & concat(Diff, ',') AS Output
RESIDENT MergedData
WHERE Not IsNull(Diff)
GROUP BY Key;


DROP Table MergedData;

[Clever_Anjos]

Maybe using a different algoritm.

Multiple steps are not necessary bad in this case, unless you have millions of records