Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to split interval time from star to end date

Hi!

I have the following data: for every employee there are his/her project and the period on which he/she is allocated.

EmployeeIDProjectStartDateEndDate
111A10/01/201431/07/2014
111B01/08/201431/05/2015
222B24/10/201431/12/2015
222C01/11/201410/03/2016

StartDate and EndDate are in time format 'dd/mm/yyyy'

I would like to split every interval StartDate-EndDate in order to get every day from start to end, for each emploeey and each project as the following table:

EmployeeIDProjectStartDate EndDateTime
111A10/01/201431/07/201410/01/2014
111A10/01/201431/07/201411/01/2014
111A10/01/201431/07/2014....
111A10/01/201431/07/201430/07/2014
111A10/01/201431/07/201431/07/2014
111B01/08/201431/05/201501/08/2014
111B01/08/201431/05/201502/08/2014
111B01/08/201431/05/2015....
111B01/08/201431/05/201530/05/2015
111B01/08/201431/05/201531/05/2015

.. and so on for EmployeeID 222.

An employee can be allocated on more than one project in the same period and an interval can overlap multiple years (as Employee 222) .

Thanks in advance for your help!

Elena

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

IntervalMatch will work, but I think a while-loop is a better way to do this. See Creating Reference Dates for Intervals

HIC

View solution in original post

3 Replies
jonasheisterkam
Partner - Creator III
Partner - Creator III

Hi, use intervalmatch() to generate the dates between and join them back.

hic
Former Employee
Former Employee

IntervalMatch will work, but I think a while-loop is a better way to do this. See Creating Reference Dates for Intervals

HIC

MarcoWedel

Hi,

one implementation using the proposed while loop:

QlikCommunity_Thread_148720_Pic1.JPG

LOAD *,

    Date(StartDate+IterNo()-1) as Time

FROM [http://community.qlik.com/thread/148720] (html, codepage is 1252, embedded labels, table is @1)

While StartDate+IterNo()-1<=EndDate;

hope this helps

regards

Marco