Convert data from rows to columns

[Joel_Holmberg]

Hi,

I'm sorry if this has been answered somewhere before. I've been searching for the last hour without success.

I'm farily new to Qlik Sense and can't get one thing to work;

The data is quite simple - the fields are "Article Number" and "Price". 

Same Article Number can have up to three different prices (sometimes one and two also).

The data output is currently

Article number, Price

A, 10

A, 20

A, 30

B, 10

B, 20

However - I'd like to display it as below;

A, 10, 20, 30

B, 10, 20, -

I asume it's the generic load I'm supposed to use - but I can't really get it to work...

I can not change the .qvd source I'm afraid. From the same .qvd I have 50 or so more fields to load but those data fields doesn't apply to this.

Any tips/suggestions would really be appriciated.

[neelamsaroha157 · Accepted solution]

You can try this in the script - 

Table1:
LOAD * INLINE [
ArticleNumber, Price

A, 10

A, 20

A, 30

B, 10

B, 20

];

NoConcatenate
LOAD ArticleNumber, ArticleNumber & ',' & Concat(Price, ',') as ConcatField Resident Table1 Group by ArticleNumber; DROP Table Table1;

[Joel_Holmberg]

Hello,

Thank you very much for the answer - really appriciate it.

How would I do this script if the dimensions and values are unknown and will change over time?

I've got thousands of entries in the tables and can't plot them like you did in your example.

Thank you.

[neelamsaroha157]

Not sure what you exactly mean. The code 'NoConcatenate
LOAD ArticleNumber, ArticleNumber & ',' & Concat(Price, ',') as ConcatField Resident Table1 Group by ArticleNumber; DROP Table Table1;' will automatically take care of all the price changes over the time.

[Joel_Holmberg]

That's great, I didn't get that 🙂

But I still don't get one thing.. The part below;

ArticleNumber, Price

A, 10

A, 20

A, 30

B, 10

B, 20

];

Do I need to enter some samples? I mean in this case I don't have information about any of the values (if A is A and if the value is 20 etc). So I can't enter this part. 

So if the only information I have is that I want to build the columns with the information from those two fields, how does the script need to look? Sorry if I'm being confusing.

[neelamsaroha157]

ArticleNumber, Price

A, 10

A, 20

A, 30

B, 10

B, 20

];

This part was only to get some testing data. You won't need this in your code because you already have data.

[Joel_Holmberg]

Worked like a charm now.

Thank you very much for your quick answers!