Announcements
cancel
Showing results 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) .

Elena

1 Solution

Accepted Solutions
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

3 Replies
Partner - Creator III

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

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

MVP

Hi,

one implementation using the proposed while loop:

```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

Community Browser