Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brindlogcool
Creator III
Creator III

Difference in Comma separated values

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.

11 Replies
petter
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

Thanks Petter,

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

petter
Partner - Champion III
Partner - Champion III

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

Clever_Anjos
Employee
Employee

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
Creator III
Creator III
Author

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
Employee
Employee

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"

];

Capturar.PNG

brindlogcool
Creator III
Creator III
Author

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
Partner - Champion III
Partner - Champion III

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
Employee
Employee

Maybe using a different algoritm.

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