topic Re: Looping Script with Variables in QlikView

Looping Script with Variables

zagzebski — Sat, 09 Nov 2013 00:27:21 GMT

I need to write a script that creates a table for every month using variables:

Basically what I need is to run the following script for every month through Oct2013. (40+ times!). Each table would be concatentated with the previous (so would have one big table at end). How can this be done so I loop through each month? I am trying to avoid writing this code 40 different times...

Table:

Load Sum(Sales)

Dept

MonthYr,

Resident Production

Group by Dept

Where MonthYr = Jan2010;

Re: Looping Script with Variables

swuehl — Sat, 09 Nov 2013 00:37:49 GMT

Maybe similar to this:

For i = 0 to 39

Let vMonthYear = date(AddMonths(makedate(2010),$(i)),'MMMYYYY');

Table:

Load Sum(Sales)

Dept,

only(MonthYr) as MonthYr,

Resident Production

Group by Dept

Where MonthYr = '$(vMonthYear)';

Next i

Re: Looping Script with Variables

preminqlik — Sat, 09 Nov 2013 00:46:00 GMT

other way is keep all files in one folder and then access from by ising '*' them like eg below

Temp_Production:

load *

from PATH/*;

Production_Final:

noconcatenate

Load Sum(Sales)

Dept

MonthYr,

Resident Temp_Production

Group by Dept,MonthYr;

drop table Temp_Production;

Re: Looping Script with Variables

andrewpettit — Sat, 09 Nov 2013 02:38:45 GMT

If you're really loading all the months from the same resident table "Production" why not simply group by Dept and MonthYr and eliminate the need for the loop?

Re: Looping Script with Variables

zagzebski — Sat, 09 Nov 2013 16:12:29 GMT

There is actually alot more to what I am trying to do - just didnt show it all. I am basically creating aggragated cubes that have the each dept's sales as a percent of the total sales for every month. It was way to hard to do on the front end.