topic Re: Pivot table data into new table during load in QlikView

Pivot table data into new table during load

magertgen — Tue, 07 Jun 2016 15:14:37 GMT

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

Re: Pivot table data into new table during load

sunny_talwar — Tue, 07 Jun 2016 15:20:20 GMT

Try this:

LOAD ID,

Product,

Concat(Date, ', ') as Dates

FROM Source

Group By ID, Product;

Re: Pivot table data into new table during load

Gysbert_Wassenaar — Tue, 07 Jun 2016 15:20:25 GMT

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

;

Re: Pivot table data into new table during load

sunny_talwar — Tue, 07 Jun 2016 15:22:50 GMT

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;

Re: Pivot table data into new table during load

magertgen — Tue, 07 Jun 2016 15:30:11 GMT

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

Re: Pivot table data into new table during load

sunny_talwar — Tue, 07 Jun 2016 15:35:08 GMT

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

Re: Pivot table data into new table during load

magertgen — Tue, 07 Jun 2016 15:39:29 GMT

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

Re: Pivot table data into new table during load

sunny_talwar — Tue, 07 Jun 2016 16:46:26 GMT

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