Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
mov
Esteemed Contributor III

Re: Looping through table and creating Dynamic columns

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

MVP & Luminary
MVP & Luminary

Re: Looping through table and creating Dynamic columns

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

Re: Looping through table and creating Dynamic columns

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.

mov
Esteemed Contributor III

Re: Re: Looping through table and creating Dynamic columns

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

Not applicable

Re: Looping through table and creating Dynamic columns

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

Not applicable

Re: Looping through table and creating Dynamic columns

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

MVP & Luminary
MVP & Luminary

Re: Re: Looping through table and creating Dynamic columns

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
mov
Esteemed Contributor III

Re: Looping through table and creating Dynamic columns

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

Re: Looping through table and creating Dynamic columns

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;