Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Looping through table and creating Dynamic columns

I have the following table ORDER

CUST_IDORD_YEARAmount
A200810
A200815
A200915
A201020
A20125
A201410
A201420
B200810
B20085
B200825
B200930
B20135
B201310
B20145

Would like to create a summary table as shown below ORD_SUMM

CUST_ID200820092010201220132014
A2515205 30
B4030 155

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;

next

drop table ORDER;

Any Idea on how to do this?

15 Replies
Anonymous
Not applicable
Author

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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...

Not applicable
Author

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);

  

next

drop table ORDER, MinMax;