4 Replies Latest reply: Sep 20, 2012 7:27 AM by Daanvand

# Complex intervalmatch ? for work schedule

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
• ###### Re: Complex intervalmatch ? for work schedule

It really must be a difficult question if this forum has no answer.

• ###### Re: Complex intervalmatch ? for work schedule

[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

[Schedule description],

Key,

Day

FROM [example.xls] (biff, embedded labels, table is [Table 2 Schedule specification\$]);

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

• ###### Re: Complex intervalmatch ? for work schedule

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

HIC

• ###### Re: Complex intervalmatch ? for work schedule

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.