Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2012-09-06 05:48 AM

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
Preview file
22 KB
551 Views
0 Likes
4 Replies
Not applicable
‎2012-09-17 03:40 AM
Author

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

260 Views
0 Likes
hic
Former Employee
Former Employee
‎2012-09-17 04:13 AM

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

260 Views
0 Likes
hic
Former Employee
Former Employee
‎2012-09-17 04:22 AM

In response to 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

260 Views
Not applicable
‎2012-09-20 07:27 AM
Author

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.

260 Views
0 Likes