topic Re: Create and Calendar from a start date to end date for different ranges in QlikView

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

Wed, 04 Sep 2013 16:16:59 GMT

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

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

DavidFoster1 — Wed, 04 Sep 2013 16:28:22 GMT

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.

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

Gysbert_Wassenaar — Wed, 04 Sep 2013 18:09:18 GMT

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.

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

Wed, 04 Sep 2013 19:47:04 GMT

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);