Script: Aggregate Orders based on Product and have a comma-separated String for Product-Configuration

[#1]

Dear Ladies and Gentlemen,

I would highly appreciate help on the topic below.

I've got Excel Spreadsheets, which have Orders with Products. Every Product could have different Configurations.

Order	Product	Configuration
100001	Maschine 1	A
100001	Maschine 1	E
100001	Maschine 1	W
100001	Maschine 2	B
100001	Maschine 2	J
100033	Maschine 1	C
100033	Maschine 1	D

I would like to read in the Data with the Qlikview Script, that it looks like the following:

Order	Product	Configuration
100001	Maschine 1	A, E, W
100001	Maschine 2	W, B, J
100033	Maschine 1	C, D

Is it's somehow the opposite of the subfield function. Is ther any chance to this?

Thank you very much for your help in advance,

Philip

[CELAMBARASAN]

Try with Concat function Philips.

Load

     Order,

     Product,

      Concat(Configuration,',') AS ConfigurationCommaSeparated

Resident

TableName;

[#3 · Accepted solution]

Dear Celambarasan Adhimulam,

I tried your proposal and the script report says the following:

Invalid expression

Variant:

Load

     [Sales doc.],

     Material,

      Concat([Variant],',') AS ConfigurationCommaSeparated

Resident Pr

I've the feeling we are very close. Maybe we need to aggregate?

[CELAMBARASAN]

Yes i missed to add Group by there

Load

     [Sales doc.],

     Material,

      Concat([Variant],',') AS ConfigurationCommaSeparated

Resident Pr

Group By

[Sales doc.], Material;

[#5]

it is working perfectly! Thank you very much.

Just one question in addtion. How would you solve this, if every Configuration would have the following:

Order	Product	Configuration
100001	Maschine 1	Maschine1_A
100001	Maschine 1	Maschine1_E
100001	Maschine 1	Maschine_W
100001	Maschine 2	Maschine2_B
100001	Maschine 2	Maschine2_J
100033	Maschine 1	Maschine1_C
100033	Maschine 1	Maschine1_D

And you want get of all the Maschinexxx_

?

[CELAMBARASAN]

You mean like this?

LOAD

          Order,

          Product,

          Concat(SubField(Configuration,Replace(Product,' ','')&'_',2),',') AS ConfigCommaSeparated

Resident

Sample

Group By

Order,Product;