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

# Packing Intervals in Qlik

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:
distinct_lgh_startdate,
distinct_lgh_enddate
;
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
;
[
%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

Maybe:

date(min(distinct_lgh_startdate)) as StartDate,

date(max(       distinct_lgh_enddate )) as EndDate

group by %tractor;

;

• ###### Re: Packing Intervals in Qlik

Dear  Justin,

EmpData:

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