topic How to split interval time from star to end date in QlikView

How to split interval time from star to end date

Anonymous — Sat, 17 Jan 2015 11:28:32 GMT

Hi!

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

EmployeeID	Project	StartDate	EndDate
111	A	10/01/2014	31/07/2014
111	B	01/08/2014	31/05/2015
222	B	24/10/2014	31/12/2015
222	C	01/11/2014	10/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:

EmployeeID	Project	StartDate	EndDate	Time
111	A	10/01/2014	31/07/2014	10/01/2014
111	A	10/01/2014	31/07/2014	11/01/2014
111	A	10/01/2014	31/07/2014	....
111	A	10/01/2014	31/07/2014	30/07/2014
111	A	10/01/2014	31/07/2014	31/07/2014
111	B	01/08/2014	31/05/2015	01/08/2014
111	B	01/08/2014	31/05/2015	02/08/2014
111	B	01/08/2014	31/05/2015	....
111	B	01/08/2014	31/05/2015	30/05/2015
111	B	01/08/2014	31/05/2015	31/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

Re: How to split interval time from star to end date

jonasheisterkam — Sat, 17 Jan 2015 11:39:21 GMT

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

Re: How to split interval time from star to end date

hic — Sat, 17 Jan 2015 12:15:18 GMT

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

HIC

Re: How to split interval time from star to end date

MarcoWedel — Sun, 18 Jan 2015 22:24:03 GMT

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