9 Replies Latest reply: Nov 6, 2013 11:20 AM by Friedrich Hofmann RSS

    A complicated case for IntervalMatch

    Friedrich Hofmann

      Hi,

       

      can someone help me please? I have a rather complicated scenario I mean to tackle with IntervalMatch - but it's not an easy win.

      The issue is about employees and their resp. Cost_Centers. Here is the scenario:

       

      - Any employee has a record in the masterdata table, with his join_date, his leave_date (which is standard 21001231 if the employee is still with us) and a Cost_Center - that never gets updated, it stays until the employee leaves.

      - His cost_center, however, might change. In case it does, the new cost_center, with a from_date and a to_date, is in another table.

      => In case the interval(s) recorded in that other tables are over, his cost_center can be switched back.

       

      => To illustrate what is the difficulty about it, I use a fictitious example:

      - There is an employee with the pers_nr 111100000

      - He/she joined on 20120801 and is with us up to today (so he/she has a leavedate of 21001231 in the masterdata table)

      - In the masterdata table, he/she has a cost_center A

      - From 20130101 to 20130830, he/she was assigned cost_center  B - that is one record in the other table

      - WITHIN that timerange, from 20130401 to 20130620, he/she was assigned cost_center C

      - From 20130831 to 21001231, he/she is assigned cost_center D

       

      In short, the timeranges as recorded in that other table are not sequential, but may be nested in one another.

      => I'd have to somehow make this several intervals. To stick with the example:

      - From 20120801 to 20121231, he/she would be assigned to cost_center A

      - From 20130101 to 20130331, he/she would  have cost_center B

      - From 20130401 to 20130620, he/she would have cost_center C

      - From 20130621 to 20130830, he/she would have cost_center B

      - From 20130831 to 21001231, he/she would have cost_center D

       

      Simple sorting of the dates won't avail me here for the intervals would be broken in that case.

      I will give this some thinking and be back with any news. Any help, however, is appreciated.

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: A complicated case for IntervalMatch
          Stefan Wühl

          Friedrich,

           

          it would make things a little easier if you could attach some sample input data in an appropriate format or even a small sample app.

           

          In principle, I think you can try something like

           

          Set DateFormat ='YYYYMMDD';

           

          INPUT:

          LOAD date#(Start) as Start, date#(End) as End, Center INLINE [

          Start, End, Center

          20120801, 20121231,  A

          20130101, 20130830,  B

          20130401, 20130620,  C

          20130831, 20131231,  D

          ];

           

          TMP:

          LOAD *, Date(Start+iterno()-1) as Date Resident INPUT while Start+iterno()-1 <= End order by Start asc;

           

          drop table INPUT;

           

          TMP2:

          LOAD *,

              if(Center<> peek(Center),rangesum(1,peek(Group)),peek(Group)) as Group;

          LOAD Date,

              LastValue(Center) as Center,

              LastValue(Start) as Start

          Resident TMP group by Date;

           

          drop table TMP;

           

          RESULT:

          LOAD Group,

              date(min(Date)) as Start,

              date(max(Date)) as End,

              only(Center) as Center

          Resident TMP2 Group by Group;

           

          drop table TMP2;

           

          You can probably already use TMP2 table for what you finally need, this is already a flat table with the Center allocation for each Date.



            • Re: A complicated case for IntervalMatch
              Friedrich Hofmann

              Hi swuehl,

               

              I'm a bit confused with your example: What is the field >Group<?

              You use that in the second LOAD, but I don't see where it comes from?

              When you can tell me that, I can tell you if you're close to what I need to do.

              In the meantime, I will put together a test_app with some sample data.

              Also, I don't quite understand the date# fct. - the integrated help_file is somehow confusing...

              I'll be back with my sample app soon.

              Best regards,

               

              DataNibbler

                • Re: A complicated case for IntervalMatch
                  Stefan Wühl

                  You should be able to just copy the code into your editor and let it load, this should create a final table which I think should match what you expect.

                   

                  Group is created with this load expression:

                   

                     if(Center<> peek(Center),rangesum(1,peek(Group)),peek(Group)) as Group

                   

                  It just numbers the consecutive sequence of dates one is belonging to the same Center.

                   

                  I think the date#() function should be explained in the Help or ref manual, and also here:

                  QlikView Date fields

                    • Re: Re: A complicated case for IntervalMatch
                      Friedrich Hofmann


                      Hi swuehl,

                       

                      I think I kind of understand. I have to give this some more thought.

                      If I understand this point correctly, however, your table TMP is just a "blowup" of what you get from INPUT.

                      => If the last interval in that table has an end_date of 21001231, that would create a lot of unneeded data, but that could be helped by replacing that with today's date.

                      Here is the sample app I put together, exactly like it is in this example - with a little twist: The last interval in that table (INPUT) has an end_date of 21001231 - but as you see in the masterdata table, that employee left on 20130831.