2 Replies Latest reply: Jul 5, 2017 7:07 AM by Arvind Patil RSS

    Packing Intervals in Qlik

    Justin Dallas

      Hello Everyone,

       

      So I have a list of ActivityDates where I need to calculate the numbers of days worked.  The core issue is that some of the dates overlap which can lead to days getting counted twice.  In the SQL world, it's called a "Packing Intevals" problem, and it's obnoxious to solve.  Below is my sample script.

       

      ActivityDates:
      LOAD DISTINCT %tractor, 
      distinct_lgh_startdate,
              distinct_lgh_enddate
      ;        
      LOAD %tractor , 
      Date(floor(TimeStamp(TimeStamp#(distinct_lgh_enddate,'M/D/YYYY h:mm')))) AS distinct_lgh_enddate,
      Date(floor(TimeStamp(TimeStamp#(distinct_lgh_startdate,'M/D/YYYY h:mm')))) AS distinct_lgh_startdate
      ;
      Load * Inline
      [
        %tractor, distinct_lgh_startdate, distinct_lgh_enddate
        679167, '6/5/2017 3:00' , '6/6/2017 12:00'
        679167, '6/5/2017 14:00' , '6/6/2017 16:00'
        679167, '6/7/2017 2:00' , '6/8/2017 3:00'
        679167, '6/6/2017 5:00' , '6/6/2017 7:00'
        679167, '6/6/2017 8:00' , '6/7/2017 22:00'
        111, '6/7/2017 3:00' , '6/10/2017 3:00'
        111, '6/7/2017 7:00' , '6/9/2017 7:00'
        111, '6/7/2017 12:00' , '6/8/2017 22:00'
      ]
      ;
      

       

       

      At this point, I've deduped the date ranges, but I don't know how to solve what I believe is the next step which would be to join the date ranges, i.e all the %tractor='111' rows disappear and are replaced with one row [111, '6/7/2017', '6/10/2017']

       

      Any help is greatly appreciated.

        • Re: Packing Intervals in Qlik
          omar bensalem

          Maybe:

           

          LOAD DISTINCT %tractor,  

          date(min(distinct_lgh_startdate)) as StartDate, 

          date(max(       distinct_lgh_enddate )) as EndDate

          group by %tractor;

          ;

          • Re: Packing Intervals in Qlik
            Arvind Patil

            Dear  Justin,

             

             

            It may help you:

             

            EmpData:

            Load * INLINE [

            EmpID, Leave, Date Start, Date End

            100, Leave, 01.01.2017, 10.01.2017

            100, Leave, 15.01.2017, 20.01.2017

            101, Leave, 10.01.2017, 20.01.2017

            102, Leave, 18.01.2017, 30.01.2017

            103, Leave, 25.01.2017, 31.01.2017

            105, Leave, 30.01.2017, 31.01.2017

            106, Leave, 10.01.2017, 12.01.2017

            107, Leave, 26.01.2017, 31.01.2017

            ];

             

            Final:

            LOAD Distinct  [EmpID], [Leave], [Date Start] as Sdate , [Date End] as Edate,

            Date([Date Start]+iterno()-1) as Date

            resident EmpData while [Date Start]+IterNo()-1 <=[Date End];

             

            drop table EmpData;

             

            Output:

             

            Emp Count.png

             

            Thanks,

            Arvind Patil