Concatenation of Column Values across Row

[#1]

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

[sunny_talwar]

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;

[maxgro]

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;

[#4]

Thanks a lot Sunny and Maxgro. Both the approaches works