Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

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:

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.

1 Solution

Accepted Solutions
arvind_patil
Valued Contributor II

Re: Packing Intervals in Qlik

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

2 Replies
OmarBenSalem
Esteemed Contributor

Re: Packing Intervals in Qlik

Maybe:

LOAD DISTINCT %tractor,  

date(min(distinct_lgh_startdate)) as StartDate, 

date(max(       distinct_lgh_enddate )) as EndDate

group by %tractor;

;

arvind_patil
Valued Contributor II

Re: Packing Intervals in Qlik

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

Community Browser