I am facing the following problem: I need to generate a table looking like this:
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;
$(Start) + Rowno() -1 as DateId
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,
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! ):
LOAD DateId as %id
for i = 1 to NoOfRows('Calendar')-1
id = peek('DateId',$(i),'Calendar');
h = 9; // just a test
Load $(id) as %id, $(h)&$(id) as test
// LOAD $(d)&$(h) as TT AutoGenerate(1);
// Resident Calendar
// Where DateId = '$(d)';