Complex intervalmatch ? for work schedule

Thu, 06 Sep 2012 09:48:33 GMT

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

Mon, 17 Sep 2012 07:40:59 GMT

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

Re: Complex intervalmatch ? for work schedule

hic — Mon, 17 Sep 2012 08:13:06 GMT

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

Re: Complex intervalmatch ? for work schedule

hic — Mon, 17 Sep 2012 08:22:46 GMT

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

Re: Complex intervalmatch ? for work schedule

Thu, 20 Sep 2012 11:27:01 GMT

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.

topic Re: Complex intervalmatch ? for work schedule in QlikView

Complex intervalmatch ? for work schedule

Re: Complex intervalmatch ? for work schedule

Re: Complex intervalmatch ? for work schedule

Re: Complex intervalmatch ? for work schedule

Re: Complex intervalmatch ? for work schedule