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

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

Tue, 04 Dec 2012 14:15:23 GMT

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

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

CELAMBARASAN — Tue, 04 Dec 2012 14:20:32 GMT

Try with Concat function Philips.

Load

Order,

Product,

Concat(Configuration,',') AS ConfigurationCommaSeparated

Resident

TableName;

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

Tue, 04 Dec 2012 14:35:39 GMT

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?

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

CELAMBARASAN — Tue, 04 Dec 2012 14:38:40 GMT

Yes i missed to add Group by there

Load

[Sales doc.],

Material,

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

Resident Pr

Group By

[Sales doc.], Material;

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

Tue, 04 Dec 2012 15:04:44 GMT

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_

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

CELAMBARASAN — Tue, 04 Dec 2012 17:50:24 GMT

You mean like this?

LOAD

Order,

Product,

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

Resident

Sample

Group By

Order,Product;