First of all i would like to thank everybody for the great input on this forum, it has been a big help for me getting used to Qlikview.
For the first time i have ran into a "problem" i couldn't solve by searching this forum (probably because i don't know how to describe the problem).
I am trying to replicate the workschedules from our employees in Qlikview.
What i'm trying to do is link the schedule to actual working times and see if there's overtime.
The problem i run into is that the schedule has a start day, for example the first line in table 1 means that the schedule starts on Tuesday (2) 26-12-2006. I want to add the schedule (Table 2) 7:30/16:00 (inz/rei) starting from day 2 and loop it ending at 31-12-9999 (or any other end date set in table 1).
Another dificulty is that the schedule in table 2 could be any number of lines long (example 1 week schedule will have 7 lines, 2 week schedule will have 14 lines etc.)
I made a example for the expected result at the bottom of my question.
Thanks in advance for helping me solve my riddle!
Regards
Daan
The data tables look like this (short example):
Table 1: Start and end date for a schedule
Start date
End date
Start day
Employee
Key
26-12-2006
31-12-9999
2
Dirk
1000000240
01-07-2008
31-12-9999
2
Kees
1000000454
Table 2: Specification for the schedule (describes the working time per day of the week 1 is monday etc)
FROM [example.xls] (biff, embedded labels, table is [Table 1 start and end date$])
While iterno() <= 100 and iterno() <= [End date] - [Start date] + 1;
//join
LOAD [Schedule nr],
[Schedule description],
Key,
Day
FROM [example.xls] (biff, embedded labels, table is [Table 2 Schedule specification$]);
Two comments:
1) You need to limit the number of records that the load statement generates. (The year 9999 would generate roughly 3 million dates.) I have limited it to 100 days per employee.
2) You wanted the result in one table. Then you need to remove the comment in front of the join. But I do not see why you need to join the two tables.
.png "Former Employee"){kind=icon}