Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data in following format :
Rank | CODE | PRODUCT | FORM | MOLECULE |
1 | ABC | QlikView | P1 | Qlik11 |
2 | ABC | QlikView | P2 | Qlik10 |
3 | ABC | QlikView | P3 | Qlik9 |
1 | XYZ | QlikSense | E1 | Sense1 |
2 | XYZ | QlikSense | E1 | Sense2 |
3 | XYZ | QlikSense | E1 | Sense3 |
4 | XYZ | QlikSense | E1 | Sense4 |
Please note the values of Columns CODE, PRODUCT,FORM are same.
Need output in below format :
Rank | CODE | PRODUCT | FORM | MOLECULE |
1 | ABC | QlikView | P1 | Qlik11+Qlik10+Qlik9 |
1 | XYZ | QlikSense | E1 | Sense1+Sense2+Sense3+Sense4 |
Kindly do let me know how I can achieve the same in QlikView using script. Thank you.
Regards,
Kumar
Try this:
LOAD 1 as Rank,
CODE,
PRODUCT,
FORM,
Concat(MOLECULE, '+') AS MOLECULE
Resident ...
Group By CODE, PRODUCT, FORM;
Table:
LOAD Rank,
CODE,
PRODUCT,
FORM,
MOLECULE
FROM
[https://community.qlik.com/thread/188441]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
NoConcatenate
LOAD FirstValue(Rank) as Rank,
CODE,
PRODUCT,
FirstValue(FORM) as FORM,
Concat(MOLECULE, '+') AS MOLECULE
Resident Table
Group By CODE, PRODUCT;
DROP Table Table;
z:
load * inline [
Rank CODE PRODUCT FORM MOLECULE
1 ABC QlikView P1 Qlik11
2 ABC QlikView P2 Qlik10
3 ABC QlikView P3 Qlik9
1 XYZ QlikSense E1 Sense1
2 XYZ QlikSense E1 Sense2
3 XYZ QlikSense E1 Sense3
4 XYZ QlikSense E1 Sense4
] (delimiter is spaces);
y:
load
Rank, CODE, PRODUCT, FORM
Resident z
where Rank=1;
left join (y)
load CODE, PRODUCT, Concat(MOLECULE, '+') as NEWMOLECULE
Resident z
group by CODE, PRODUCT;
DROP Table z;
Thanks a lot Sunny and Maxgro. Both the approaches works