topic Re: Difference in Comma separated values in QlikView

Difference in Comma separated values

brindlogcool — Tue, 01 Mar 2016 20:18:50 GMT

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.

Re: Difference in Comma separated values

petter — Tue, 01 Mar 2016 20:24:29 GMT

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

];

Re: Difference in Comma separated values

brindlogcool — Tue, 01 Mar 2016 20:28:17 GMT

Thanks Petter,

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

Re: Difference in Comma separated values

petter — Tue, 01 Mar 2016 20:34:18 GMT

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

Re: Difference in Comma separated values

Clever_Anjos — Tue, 01 Mar 2016 20:38:03 GMT

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"
];

Re: Difference in Comma separated values

brindlogcool — Tue, 01 Mar 2016 21:12:48 GMT

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

Re: Difference in Comma separated values

Clever_Anjos — Tue, 01 Mar 2016 21:20:49 GMT

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"
];

Re: Difference in Comma separated values

brindlogcool — Tue, 01 Mar 2016 21:34:14 GMT

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.

Re: Difference in Comma separated values

Peter_Cammaert — Tue, 01 Mar 2016 22:14:35 GMT

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;

Re: Difference in Comma separated values

Clever_Anjos — Wed, 02 Mar 2016 11:25:39 GMT

Maybe using a different algoritm.

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

Re: Difference in Comma separated values

brindlogcool — Wed, 02 Mar 2016 16:43:28 GMT

Does evaluate works on date/timestamp?

Re: Difference in Comma separated values

Clever_Anjos — Wed, 02 Mar 2016 16:59:26 GMT

Yes, it does