Create and Calendar from a start date to end date for different ranges

[#1]

Hi Everyone,

I have a list of users that are required to enter time in our time tracking system.

Each person on a list has a join date and some have a leave date

USERID              START               END               HOURSPERDAY

JONES               01/09/2013          TODAY()                7

BROWN             01/02/2013          01/08/2013             8 

etc....

I need to create table that has a record per day in between each of the dates above

Today being the 04/09 then Jones would have 4 rows

JONES     01/09/2013          7

JONES     02/09/2013          7

etc.

JONES     04/09/2013          7

I have some notional ideas around autogenerate and looping but thats about it I am afraid.

Can someone help me out and add to my Qlikview knowledge.

Thanks in advance,

Rob

[DavidFoster1]

If you have a calendar table, then you can use an intervalmatch join function to create a new table with a record for every day between your START and END dates.

[Gysbert_Wassenaar · Accepted solution]

Try:

Input:

LOAD * INLINE [

    USERID, START, END, HOURSPERDAY

    JONES, 01/09/2013, TODAY() ,7

    BROWN, 01/02/2013, 01/08/2013, 8

];

Result:

LOAD USERID, date(START + IterNo()-1) as DATE, HOURSPERDAY as HOURS

Resident Input

while START + IterNo() -1 <= if(END='TODAY()',Today(),END);

Note, the inline load is just an example. Replace it with a load statement that loads your real source date.

[#4]

Thanks that got the job done - minor caveat and I didn't explain (at all) correctly,I was using today as my end date if the employee was still hired

I do appreciate very much you taking the time to attach a working version.

My Final script

Input:

LOAD

  USR_USER,

     START,

     if(isNull(END),today(),END) as END,

     HOURSPERDAY

FROM

E:\SkyDrive\Qlikview\Data\Temp.xlsx (ooxml, embedded labels, table is Sheet1);

Result:

LOAD USR_USER, date(START + IterNo()-1) as DATE, HOURSPERDAY as HOURS

Resident Input

while START + IterNo() -1 <= if(END='TODAY()',Today(),END);