Expand Date Range into Individual Days

[Anonymous]

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!

[johnw]

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/

[rwunderlich]

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

[Oleg_Troyansky · Accepted solution]

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

[Oleg_Troyansky]

Rob - this is just funny 🙂

[rwunderlich]

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

[Anonymous]

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!

[#8]

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

[johnw]

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;

[#10]

Thanks John