Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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.

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

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

OrderProductConfiguration
100001Maschine 1A, E, W
100001Maschine 2W, B, J
100033Maschine 1C, 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

Tags (3)
1 Solution

Accepted Solutions
Not applicable

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

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?

5 Replies

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

Try with Concat function Philips.

Load

     Order,

     Product,

      Concat(Configuration,',') AS ConfigurationCommaSeparated

Resident

TableName;

Not applicable

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

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

Yes i missed to add Group by there

Load

     [Sales doc.],

     Material,

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

Resident Pr

Group By

[Sales doc.], Material;

Not applicable

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

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:

OrderProduct
Configuration
100001Maschine 1Maschine1_A
100001Maschine 1Maschine1_E
100001Maschine 1Maschine_W
100001Maschine 2Maschine2_B
100001Maschine 2Maschine2_J
100033Maschine 1Maschine1_C
100033Maschine 1Maschine1_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

You mean like this?

LOAD

          Order,

          Product,

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

Resident

Sample

Group By

Order,Product;

Community Browser