Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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 dateEnd dateStart dayEmployeeKey
26-12-200631-12-99992Dirk1000000240
01-07-200831-12-99992Kees1000000454

Table 2: Specification for the schedule (describes the working time per day of the week 1 is monday etc)

Schedule nrSchedule descriptionKeyDay
10000002207:30/16:00 (inz/rei)10000002401
10000002207:30/16:00 (inz/rei)10000002402
10000002207:30/16:00 (inz/rei)10000002403
10000002207:30/16:00 (inz/rei)10000002404
10000002207:30/16:00 (inz/rei)10000002405
10000000207:30/16:00 (inz/rei)10000002406
10000000207:30/16:00 (inz/rei)10000002407
10000002207:30/16:00 (inz/rei)10000002408
10000002207:30/16:00 (inz/rei)10000002409
10000002207:30/16:00 (inz/rei)100000024010
10000002207:30/16:00 (inz/rei)100000024011
10000002207:30/16:00 (inz/rei)100000024012
10000000207:30/16:00 (inz/rei)100000024013
10000000207:30/16:00 (inz/rei)100000024014
1000000164Food10000004541
1000000164Food10000004542
1000000164Food10000004543
1000000164Food10000004544
1000000081Food10000004545
1000000020Food10000004546
1000000020Food10000004547

Wanted result

DateSchedule nrSchedule descriptionKeyEmployee
26-12-200610000002207:30/16:00 (inz/rei)1000000240Dirk
27-12-200610000002207:30/16:00 (inz/rei)1000000240Dirk
28-12-200610000002207:30/16:00 (inz/rei)1000000240Dirk
29-12-200610000002207:30/16:00 (inz/rei)1000000240Dirk
30-12-200610000000207:30/16:00 (inz/rei)1000000240Dirk
31-12-200610000000207:30/16:00 (inz/rei)1000000240Dirk
01-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
02-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
03-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
04-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
05-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
06-01-200710000000407:30/16:00 (inz/rei)1000000240Dirk
07-01-200710000000407:30/16:00 (inz/rei)1000000240Dirk
08-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
09-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
10-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
11-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
12-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
13-01-200710000000207:30/16:00 (inz/rei)1000000240Dirk
14-01-200710000000207:30/16:00 (inz/rei)1000000240Dirk
15-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
16-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
17-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
18-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
19-01-200710000002207:30/16:00 (inz/rei)1000000240Dirk
20-01-200710000000407:30/16:00 (inz/rei)1000000240Dirk
21-01-200710000000407:30/16:00 (inz/rei)1000000240Dirk
01-07-20081000000164Food1000000454Kees
02-07-20081000000164Food1000000454Kees
03-07-20081000000164Food1000000454Kees
04-07-20081000000081Food1000000454Kees
05-07-20081000000020Food1000000454Kees
06-07-20081000000020Food1000000454Kees
07-07-20081000000164Food1000000454Kees
08-07-20081000000164Food1000000454Kees
09-07-20081000000164Food1000000454Kees
10-07-20081000000164Food1000000454Kees
11-07-20081000000081Food1000000454Kees
12-07-20081000000020Food1000000454Kees
13-07-20081000000020Food1000000454Kees
14-07-20081000000164Food1000000454Kees
15-07-20081000000164Food1000000454Kees
16-07-20081000000164Food1000000454Kees
17-07-20081000000164Food1000000454Kees
18-07-20081000000081Food1000000454Kees
19-07-20081000000020Food1000000454Kees
20-07-20081000000020Food1000000454Kees
21-07-20081000000164Food1000000454Kees
22-07-20081000000164Food1000000454Kees
23-07-20081000000164Food1000000454Kees
24-07-20081000000164Food1000000454Kees
25-07-20081000000081Food1000000454Kees
26-07-20081000000020Food1000000454Kees
27-07-20081000000020Food1000000454Kees
4 Replies
Not applicable

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

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

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

Not applicable

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.

Community Browser