Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Try with Concat function Philips.
Load
Order,
Product,
Concat(Configuration,',') AS ConfigurationCommaSeparated
Resident
TableName;
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?
Yes i missed to add Group by there
Load
[Sales doc.],
Material,
Concat([Variant],',') AS ConfigurationCommaSeparated
Resident Pr
Group By
[Sales doc.], Material;
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_
?
You mean like this?
LOAD
Order,
Product,
Concat(SubField(Configuration,Replace(Product,' ','')&'_',2),',') AS ConfigCommaSeparated
Resident
Sample
Group By
Order,Product;