topic Re: Looping through table and creating Dynamic columns in QlikView

Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 16:35:37 GMT

I have the following table ORDER

CUST_ID	ORD_YEAR	Amount
A	2008	10
A	2008	15
A	2009	15
A	2010	20
A	2012	5
A	2014	10
A	2014	20
B	2008	10
B	2008	5
B	2008	25
B	2009	30
B	2013	5
B	2013	10
B	2014	5

Would like to create a summary table as shown below ORD_SUMM

CUST_ID	2008	2009	2010	2012	2013	2014
A	25	15	20	5		30
B	40	30			15	5

Tried the for loop but its throwing error.

Let vYRVal = min(ORD_YEAR);

for i=min(ORD_YEAR) to year(Today(0))

ORD_SUMM:

load

CUST_ID,

SUM(Amount) as $(vYRVal)

Resident

ORDER

where

ORD_YEAR = $(vYRVal)

group by

CUST_ID;

vYRVal = $(vYRVal)+1;

drop table ORDER;

Any Idea on how to do this?

Re: Looping through table and creating Dynamic columns

Anonymous — Tue, 18 Mar 2014 16:48:41 GMT

Do you have to do it in the script? If not, see attached.

Re: Looping through table and creating Dynamic columns

Gysbert_Wassenaar — Tue, 18 Mar 2014 16:57:29 GMT

And if you need a script based solution you can use a generic load. Optionally joining the resulting tables into one table. This can cause performance problems on large datasets. See attached qvw.

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 16:58:50 GMT

Thanks , But I need a resident table. I need to do some analysis on accounts and I am creating a summary table.

At the end I need to do an export of 300-400K records to CSV with 10-15 dimension and few facts. I am getting a out of memory error if I do a pivot table . So I thought I will create a straight table with pre-calculated facts.

Re: Re: Looping through table and creating Dynamic columns

Anonymous — Tue, 18 Mar 2014 17:31:50 GMT

One more version... But I added multiple conditional expressions.

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 18:06:02 GMT

Thanks Gysbert! The only thing is I need only 1 record per CUST_ID

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 18:12:13 GMT

Micheal,

I looked at QVW and bit confused , can we do the same by the following lines ?

[ORD_SUM]:

NoConcatenate load CUST_ID ,ORD_YEAR as Year, SUM(Amount) as Amount Resident ORDER group by CUST_ID ,ORD_YEAR;

drop table ORDER;

What i am trying to do is get 1 record by CUST_ID , then years as column

Thanks,

Aji

Re: Re: Looping through table and creating Dynamic columns

Gysbert_Wassenaar — Tue, 18 Mar 2014 18:19:59 GMT

Ah, I missed that you had unaggregated detail records. Just sum those up first:

Temp:

Generic

LOAD CUST_ID, ORD_YEAR, sum(Amount)

Group by CUST_ID, ORD_YEAR;

Load ...etc

See attached qvw.

Re: Looping through table and creating Dynamic columns

Anonymous — Tue, 18 Mar 2014 18:23:47 GMT

Yes, it is the same thing.

(I tried to "fix" your original script and got a more complex than it needs to be )

I assume you want the complete table image in the data model. That means it will be a table box on the front end, not a straight table. Correct?

Still the question remains how dynamically add the columns, even in the table box. So, I think the straight table with conditional expression is a working solution.

Let's see what Gysbert has...

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 18:37:05 GMT

Actually your script helped. Made little change an look like working..

Please see below

MinMax:

LOAD

min(ORD_YEAR) as MinYear,

max(ORD_YEAR) as MaxYear

RESIDENT ORDER;

ORD_SUM:

LOAD * INLINE [

CUST_ID];

for i=peek('MinYear',0,'MinMax') to peek('MaxYear',0,'MinMax')

join (ORD_SUM)

load

CUST_ID,

// $(i) as Year,

// SUM(Amount) as Amount

SUM(Amount) as $(i)

Resident ORDER

where ORD_YEAR = $(i)

group by CUST_ID, $(i);

drop table ORDER, MinMax;

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 18:42:08 GMT

Thanks Gysbert!

Still the back end data has multiple records

Re: Looping through table and creating Dynamic columns

Anonymous — Tue, 18 Mar 2014 18:45:13 GMT

Makes sense.

(BTW, you don't need to group by $(i) anymore, although no harm either.)

The only remaining question is what to do with the dynamic front end.

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 18:45:31 GMT

That helps even better for 2011 there were no data and now I have blank column for that .

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 18:53:32 GMT

This is mostly 1 time deal. But I have similar reports where I dont use year as column name rather the difference between the current year and order year prefixed with YR as column name and the title for YR_0 ( which is 2014) will be year(Today()) and for YR_1 (i.e 2013) will be (year(Today())-1) and so forth. The table is limited to past 5 years.

Re: Re: Looping through table and creating Dynamic columns

Gysbert_Wassenaar — Tue, 18 Mar 2014 20:08:08 GMT

Are you sure you opened the correct version? Perhaps saving it under a different name helps.

Re: Looping through table and creating Dynamic columns

Tue, 18 Mar 2014 22:09:54 GMT

Sorry, I was looking at the old one..