topic Expand Date Range into Individual Days in QlikView

Expand Date Range into Individual Days

Anonymous — Sat, 25 Jul 2009 03:39:46 GMT

My first post on the forums so hopefully the solution to my question isn't too obvious! I have an existing table (VACATION) which is used to track vacation time by employee. The layout is very simple:

VACATION:

Employee_ID, Vacation_Start, Vacation_End

1, 1/28/2009, 2/5/2009

What I need to do is create another table (VACATION_DATE) with a row for every day the employee is on vacation. For example if we had an employee (ID #1) with vacation that starts on 1/28/2009 and ends on 2/5/2009 the output would be:

VACATION_DATE:

Employee_ID, Vacation_Date

1, 1/28/2009

1, 1/29/2009

1, 1/30/2009

1, 1/31/2009

1, 2/1/2009

1, 2/2/2009

1, 2/3/2009

1, 2/4/2009

1, 2/5/2009

I tried using a For Loop within a Load statement but can't seem to figure out the correct syntax. Any help would be appreciated!

Expand Date Range into Individual Days

johnw — Sat, 25 Jul 2009 04:05:55 GMT

I don't have QlikView in front of me at the moment to test, but I believe you need intervalmatch(). I believe that Rob Wunderlich has an example in his cookbook, available at:

http://robwunderlich.com/downloads/

Expand Date Range into Individual Days

rwunderlich — Sat, 25 Jul 2009 04:18:55 GMT

The IntervalMatch approach is great if you want to link the vacations into a calendar of days, like the entire year. This may be what you want. The QV Cookbook example that John referenced is titled "Fill values in a data range using previous values.",

However, if you want to do exactly what you asked, that is, generate a row for each day only within the actual vacation ranges, then an easier way might be:

Vacation_Days:
LOAD
Employee_ID,
date(Vacation_Start + IterNo() -1) as Vacation_Date
RESIDENT VACATION
WHILE Vacation_Start + IterNo() -1 <= Vacation_End
;

-Rob

Expand Date Range into Individual Days

Oleg_Troyansky — Sat, 25 Jul 2009 04:20:17 GMT

You need to use a LOAD with the WHILE clause and a function IterNo(). For each row in the source table, the logic will be repeated multiple times, as long as (WHILE) the condition is true. IterNo() is the number of the iteration - use it to exit from this loop

Your LOAD should look like this:


VACATION_DATE:
load
 Employee_ID,
 Vacation_Start + IterNo() - 1 as Vacation_Date
resident
 VACATION
WHILE
 Vacation_Start + IterNo() - 1 <= Vacation_End
;

Cheers,

Oleg

Expand Date Range into Individual Days

Oleg_Troyansky — Sat, 25 Jul 2009 04:21:47 GMT

Rob - this is just funny 🙂

Expand Date Range into Individual Days

rwunderlich — Sat, 25 Jul 2009 04:37:34 GMT

For the record, here's an IntervalMatch solution that is clearer than the Cookbook example for this dataset.

VACATION:
LOAD * INLINE [
Employee_ID, Vacation_Start, Vacation_End
1, 1/28/2009, 2/5/2009
2, 6/1/2009, 6/15/2009
]
;

Dates:
LOAD date(mindate + IterNo() -1) as Vacation_Date
WHILE mindate + IterNo() -1 <= maxdate
;
LOAD min(Vacation_Start) as mindate, max(Vacation_End) as maxdate
RESIDENT VACATION
;

LEFT JOIN (VACATION) IntervalMatch (Vacation_Date)
LOAD Vacation_Start, Vacation_End
RESIDENT VACATION
;

(This will be followed in a few moments by a similar post from Oleg 🙂 )

-Rob

Expand Date Range into Individual Days

Anonymous — Sat, 25 Jul 2009 04:53:07 GMT

Oleg,

This works great, it seems so simple once you see the solution. I am also experimenting with the other suggested answer to use IntervalMatch. Thanks for your help!

Expand Date Range into Individual Days

Wed, 06 Oct 2010 10:25:50 GMT

I am having a similar difficulty. In my case, I had to list monthly payments given FROM_MONTH, FROM_YEAR to TO_MONTH, TO_YEAR, as follows:

PAYMENTS:
LOAD * INLINE [
SID, FROM_MONTH, FROM_YEAR, TO_MONTH, TO_YEAR
1, 1, 2009, 3, 2009
2, 11, 2009, 4, 2010
]
;

Result would be:

SID, MONTHS_PAID

1,Jan-2009

1,Feb-2009

1,Mar-2009

2,Nov-2009

2,Dec-2009

2,Jan-2010

2,Feb-2010

2,Mar-2010

2,Apr-2010

Can you help me pls.

Thanks

Expand Date Range into Individual Days

johnw — Wed, 06 Oct 2010 18:52:26 GMT

PAYMENTS:
LOAD SID
,date(addmonths(makedate(FROM_YEAR,FROM_MONTH),iterno()-1),'MMM-YYYY') as MONTHS_PAID
INLINE [
SID, FROM_MONTH, FROM_YEAR, TO_MONTH, TO_YEAR
1, 1, 2009, 3, 2009
2, 11, 2009, 4, 2010
] WHILE iterno() <= (TO_YEAR - FROM_YEAR)*12 + TO_MONTH - FROM_MONTH + 1;

Expand Date Range into Individual Days

Thu, 07 Oct 2010 03:54:09 GMT

Thanks John