Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
magertgen
Contributor III
Contributor III

Pivot table data into new table during load

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:

IDProductDate
1A1/1/2016
1A2/1/2016
1A3/1/2016
1B1/1/2016
1B

4/1/2016

1C1/1/2016
2A1/1/2016
2A2/1/2016
2D3/1/2016
2D4/1/2016

I want a new table like this:

IDProductDates
1A1/1/2016, 2/1/2016, 3/1/2016
1B1/1/2016, 4/1/2016
1C1/1/2016
2A1/1/2016, 2/1/2016
2D3/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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

;


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
sunny_talwar

Try this:

LOAD ID,

          Product,

          Concat(Date, ', ') as Dates

FROM Source

Group By ID, Product;

Gysbert_Wassenaar

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

;


talk is cheap, supply exceeds demand
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;


Capture.PNG

magertgen
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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