Packing Intervals in Qlik

[JustinDallas]

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.

[OmarBenSalem]

Maybe:

LOAD DISTINCT %tractor,  

date(min(distinct_lgh_startdate)) as StartDate, 

date(max(       distinct_lgh_enddate )) as EndDate

group by %tractor;

;

[arvind_patil · Accepted solution]

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:

Thanks,

Arvind Patil