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

    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
        • 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
            Henric Cronström

            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

              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.