Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
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)
Schedule nr | Schedule description | Key | Day |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 1 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 2 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 3 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 4 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 5 |
1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | 6 |
1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | 7 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 8 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 9 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 10 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 11 |
1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | 12 |
1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | 13 |
1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | 14 |
1000000164 | Food | 1000000454 | 1 |
1000000164 | Food | 1000000454 | 2 |
1000000164 | Food | 1000000454 | 3 |
1000000164 | Food | 1000000454 | 4 |
1000000081 | Food | 1000000454 | 5 |
1000000020 | Food | 1000000454 | 6 |
1000000020 | Food | 1000000454 | 7 |
Wanted result
Date | Schedule nr | Schedule description | Key | Employee |
26-12-2006 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
27-12-2006 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
28-12-2006 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
29-12-2006 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
30-12-2006 | 1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
31-12-2006 | 1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
01-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
02-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
03-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
04-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
05-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
06-01-2007 | 1000000040 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
07-01-2007 | 1000000040 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
08-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
09-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
10-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
11-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
12-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
13-01-2007 | 1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
14-01-2007 | 1000000020 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
15-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
16-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
17-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
18-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
19-01-2007 | 1000000220 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
20-01-2007 | 1000000040 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
21-01-2007 | 1000000040 | 7:30/16:00 (inz/rei) | 1000000240 | Dirk |
01-07-2008 | 1000000164 | Food | 1000000454 | Kees |
02-07-2008 | 1000000164 | Food | 1000000454 | Kees |
03-07-2008 | 1000000164 | Food | 1000000454 | Kees |
04-07-2008 | 1000000081 | Food | 1000000454 | Kees |
05-07-2008 | 1000000020 | Food | 1000000454 | Kees |
06-07-2008 | 1000000020 | Food | 1000000454 | Kees |
07-07-2008 | 1000000164 | Food | 1000000454 | Kees |
08-07-2008 | 1000000164 | Food | 1000000454 | Kees |
09-07-2008 | 1000000164 | Food | 1000000454 | Kees |
10-07-2008 | 1000000164 | Food | 1000000454 | Kees |
11-07-2008 | 1000000081 | Food | 1000000454 | Kees |
12-07-2008 | 1000000020 | Food | 1000000454 | Kees |
13-07-2008 | 1000000020 | Food | 1000000454 | Kees |
14-07-2008 | 1000000164 | Food | 1000000454 | Kees |
15-07-2008 | 1000000164 | Food | 1000000454 | Kees |
16-07-2008 | 1000000164 | Food | 1000000454 | Kees |
17-07-2008 | 1000000164 | Food | 1000000454 | Kees |
18-07-2008 | 1000000081 | Food | 1000000454 | Kees |
19-07-2008 | 1000000020 | Food | 1000000454 | Kees |
20-07-2008 | 1000000020 | Food | 1000000454 | Kees |
21-07-2008 | 1000000164 | Food | 1000000454 | Kees |
22-07-2008 | 1000000164 | Food | 1000000454 | Kees |
23-07-2008 | 1000000164 | Food | 1000000454 | Kees |
24-07-2008 | 1000000164 | Food | 1000000454 | Kees |
25-07-2008 | 1000000081 | Food | 1000000454 | Kees |
26-07-2008 | 1000000020 | Food | 1000000454 | Kees |
27-07-2008 | 1000000020 | Food | 1000000454 | Kees |
It really must be a difficult question if this forum has no answer.
LOAD [Start date],
[End date],
Date([Start date] + iterno() - 1) as Date,
[Start day],
Employee,
Key
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.
HIC
PS
I just saw that you had a second key between the tables; Day. To link over this key as well as the other Key, you just need to add
iterno() as Day
to the first Load.
HIC
Thanks for the input!
Now i have a date range in the start and end date schedule table.
The only problem is that i want to connect it to the specification schedule table with the start day and not just with the key.
I now have several schedule nr's per date while i should have only one schedule a day.
I think i should create some key based on the field key and start date.