Pivot table data into new table during load

[magertgen]

Hey all, I'm trying to figure out how to take data from an existing table and pivot it into a new table during my load.

For example, I have ID, Product and Date in an existing table:

ID	Product	Date
1	A	1/1/2016
1	A	2/1/2016
1	A	3/1/2016
1	B	1/1/2016
1	B	4/1/2016
1	C	1/1/2016
2	A	1/1/2016
2	A	2/1/2016
2	D	3/1/2016
2	D	4/1/2016

I want a new table like this:

ID	Product	Dates
1	A	1/1/2016, 2/1/2016, 3/1/2016
1	B	1/1/2016, 4/1/2016
1	C	1/1/2016
2	A	1/1/2016, 2/1/2016
2	D	3/1/2016, 4/1/2016

I know I have to loop over the original table, but I'm having hard time figuring out how to create a new table while doing so.

If anyone can offer any help, I'd sure appreciate it!

Thanks in advance,

~Mark

[sunny_talwar]

Try this:

LOAD ID,

          Product,

          Concat(Date, ', ') as Dates

FROM Source

Group By ID, Product;

[Gysbert_Wassenaar · Accepted solution]

Easier to keep the data as it is and use a chart expression. Use ID and Product as dimensions and concat(Date, ', ', Date) as expression.

If you insist on mucking up the data model to get that useless Dates field then do this:

MyNewTable:

LOAD ID, Product, concat(Date, ', ', Date) as Dates

RESIDENT MyExistingTable

GROUP BY ID, Product

;

[sunny_talwar]

This is for your sample:

Table:

LOAD ID,

    Product,

    Concat(Date, ', ') as Dates

FROM

[https://community.qlik.com/thread/219840]

(html, codepage is 1252, embedded labels, table is @1)

Group By ID, Product;

[magertgen]

Brilliant!  Thank you both for your quick responses!

"If you insist on mucking up the data model to get that useless Dates field then do this:"

That's kinda mean but it made me laugh!  I've never had to do something like this before so the concat function hasn't been on my radar until now.

Thanks!

~Mark

[sunny_talwar]

Just because I was not able to make you laugh you didn't give me the correct answer . That seems mean as well

[magertgen]

My apologies, if I could mark both as correct I would have!  Using the concat function in a chart actually IS a better solution than adding a table to the data model so that's why I marked his as correct.  It's just slightly more correct, that's all!

~Mark

[sunny_talwar]

I guess my response was focused at getting you what you asked for

"Pivot table data into new table during load"

But as long as you got what you wanted, we are all happy

Best,

Sunny