Generating a table using a loop

Wed, 06 Mar 2013 18:07:52 GMT

Hello,

I am facing the following problem: I need to generate a table looking like this:

Date	0:00	...	10:00	11:00	12:00	13:00	...	23:00
01/01/13	0	0	4	4	4	4	0	0
02/01/13	...		3	3	3	4	...	..
...	...		...	...	...	...	..	...
31/12/13	0		2	2	2	0	0	...

Row are dates.

Columns are hours of the day (0...23)

The numbers inside the tables are the amount of people available ( that´s the challenge!)

I need to build this and I have:

- a table with users including names and country

- a table of bank holidays per country

- a table with working time per country

- a table of holidays per user

To get started, I generated a calendar to cover the range I need.

Loading the tables above is no problem of course.

Can you explain what would be the best approach to fill this table ?

My idea (but I miss some elements to acheive it) is to:

- generate a calendar => done

- load all the data => done

- use for loop to loop thru all the days => done

- use a nested for loop for the hours (from 0 to 23) => should be ok

- another for loop to go thru the users and check if the user is availble, that day, at that time (can we define functions in Qlikview ?)

- finally sum the results per user (or keep separate tables)

- put all of that together !

The function above will need to check out value from all the table to define if a user is available or not.

The first problem I hit is how to create a table with data from a for loop (I proudly acheived to generate 365 tables dynamically but that does not help me )

How would you approach this problem? Does Qlikview scripting makes sense in that case?

My Calendar is generated as (this part is probably OK):

LET Start = floor(YearStart(AddMonths(today(), -12*0))); // *N = N year in the past

LET End = floor(YearEnd(AddMonths(today(), 12*0))); // *N = N year in the future

LET NumOfDays = End - Start + 1;

Date_src:

LOAD

$(Start) + Rowno() -1 as DateId

AUTOGENERATE $(NumOfDays);

Calendar:

LOAD

date(DateId) as Date, // it will be in format defined in your SET DateFormat=, or in your system format

day(DateId) as Day,

month(DateId) as Month, // simple month name; it is dual - numeric and text

dual(month(DateId) & '-' & year(DateId), year(DateId) & num(month(DateId), '00')) as MonthYear, // Month-Year format, dual

dual(month(DateId) & '-' & day(DateId), year(DateId) & num(month(DateId), '00') & num(day(DateId), '00')) as MonthDay, // Month-Year format, dual

year(DateId) as Year,

RESIDENT Date_src;

Drop Table Date_src;

LET Start = null();

LET End = null();

LET NumOfDays = null();

For now, I am messing around with (dont use that at home! 😞

Availability:

LOAD DateId as %id

resident Calendar;

for i = 1 to NoOfRows('Calendar')-1

id = peek('DateId',$(i),'Calendar');

h = 9; // just a test

JOIN

Load $(id) as %id, $(h)&$(id) as test

AutoGenerate(1);

// ConcatTable:

// LOAD $(d)&$(h) as TT AutoGenerate(1);

// Resident Calendar

// Where DateId = '$(d)';

topic Generating a table using a loop in QlikView

Generating a table using a loop