Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm looking to create a holiday calendar via a pivot table. For each employee and holiday period I have a start date and an end date but I need to infill the dates between.
Ie
Employee | Start Date | End Date |
---|---|---|
Gavin | 1/3/2014 | 15/3/2014 |
Dean | 15/3/2014 | 30/03/2014 |
To be able to show this in a calendar I need to create a holiday record for the days in between
Name | Header 2 |
---|---|
Gavin | 01/03/2014 |
Gavin | 02/03/2014 |
Gavin | 03/03/2014 |
Gavin | 04/03/2014 |
Dean | 15/03/2014 |
Dean | 16/03/2014 |
Dean | 17/03/2014 |
Obviously I've not added all the dates in this second table but hopefully you get the idea of where I'm trying to get to. I've also added a sample of the holiday calendar that I'm trying to recreate. Any advice would be greatly appreciated
Use enclosed file which can help you to fill missing data...
Thanks for your reply Manisha. In reality my data is slightly different. Will your example still work if my data is as below?
TempTable:
LOAD * Inline
[
Item, Start Date, End Date Price
A, 01/10/2010, 10/10/2010, 100
A 15/11/2010, 19/11/2010, 200
B, 03/10/2010, 04/10/2010, 100
B, 08/10/2010, 14/10/2010, 400
];
T:
load * inline [
Employee, StartDate, EndDate
Gavin, 1/3/2014 ,15/3/2014
Dean, 15/3/2014 ,30/03/2014
];
T2:
NoConcatenate load
Employee,
date(StartDate + iterno() -1) as Date
resident T
while iterno() <= EndDate - StartDate +1;