Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data in the following format:
TransID | ProductID |
1 | a |
1 | b |
1 | c |
2 | b |
2 | d |
2 | e |
2 | f |
Need to transform into the following:
TransID | Products |
1 | a, b, c |
2 | b, d, e, f |
Use Concat() and Group By:
LOAD
TransID,
Concat(ProductID, ', ') as Products
Inline [
TransID ProductID
1 a
1 b
1 c
2 b
2 d
2 e
2 f
] (delimiter is '\t')
Group by TransID
;
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
you can use Generic load .
https://community.qlik.com/t5/Design/The-Generic-Load/ba-p/1473470
hope this is useful!
// load the original table
LOAD
TransID,
ProductID
FROM data.csv (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
// group by TransID and concatenate ProductIDs
LOAD
TransID,
concat(ProductID, ', ') AS Products
RESIDENT
OriginalTable
GROUP BY
TransID;
// drop the original table
DROP TABLE OriginalTable;