Master Calendar based on from to range of fact table records

[tschullo]

I have a table that has multiple rows per Name because the Amount filed varies according to a set of effective dates, Date1 (start), and Date2 (expire).  I am trying to create a Master Calendar table that links to this table and allows me to select a date and any records that cover that date (between Date1 and Date2) are shown.

In essence I need to loop through each record and create a date for every day between the from and to.

Something is wrong with my script and I don't seem to be iterating through all my records.

Please help.

Tab1:
Load *,
Name & ';' & Date1 & ':' & Date2 as DATE_LINK;

LOAD Name,Date(Date#(Date1,'MM/DD/YYYY')) as Date1,Date(Date#(Date2,'MM/DD/YYYY')) as Date2,Amount INLINE [
Name, Date1, Date2, Amount
Tom, 1/2/2001, 1/4/2007, 545
Joey, 1/2/2004, 5/2/2007, 640
Carl, 2/4/2012, 2/4/2006, 220
Paul, 6/14/2012,1/2/2013 , 115
Max, 1/1/2010,1/2/2011, 302
Dev, 1/2/2001, 12/11/2008, 282
Tom, 1/5/2007, 1/4/2014, 535
Joey, 5/3/2007, 5/2/2012, 600
Carl, 2/5/2006, 2/4/2012, 200
Paul, 1/3/2013,1/2/2014 , 125
Max, 1/3/2011,1/2/2015, 332
Dev, 12/12/2008, 12/11/2014, 382
];

  Temp:
Load Max(Date1) as Max,Min(Date1) as Min Resident Tab1;
  Load Max(Date2) as Max,Min(Date2) as Min Resident Tab1;

MaxMin:
Load Max(Max) as MAX, min(Min) as MIN Resident Temp;
  Drop table Temp;

Let vMinDate = num(Peek('MIN',0,'MaxMin'));
Let vMaxDate = num(Peek('MAX',0,'MaxMin'));

Drop table MaxMin;
tab1Dates:
Load Name & ';' & Date1 & ':' & Date2 as DATE_LINK,
date($(vMinDate) + RowNo() -1) as Temp_Date
Resident Tab1
While date($(vMinDate) + RowNo() -1) < date($(vMaxDate))

;
MasterCalendar:
Load Distinct DATE_LINK,
Temp_Date as RptDate,
Month(Temp_Date) as Month,
Year(Temp_Date) as Year
Resident tab1Dates;

Drop table tab1Dates;

[sunny_talwar]

Great, I did post the reason for discrepancy above. I guess you might have missed it

[tschullo · Accepted solution]

So with a few alterations I came up with what I wanted. thaks for the right direction!

Tab1:
LOAD Name,
Name & ':' & Date1 & ':' & Date2 as DATE_LINK,
Date(Date#(Date1,'MM/DD/YYYY')) as Date1,
Date(Date#(Date2,'MM/DD/YYYY')) as Date2,
Amount
INLINE [
Name, Date1, Date2, Amount
Tom, 1/2/2001, 1/4/2007, 545
Joey, 1/2/2004, 5/2/2007, 640
Carl, 2/4/2005, 2/4/2006, 220
Paul, 6/14/2012,1/2/2013 , 115
Max, 1/1/2010,1/2/2011, 302
Dev, 1/2/2001, 12/11/2008, 282
Tom, 1/5/2007, 1/4/2014, 535
Joey, 5/3/2007, 5/2/2012, 600
Carl, 2/5/2006, 2/4/2012, 200
Paul, 1/3/2013,1/2/2014 , 125
Max, 1/3/2011,1/2/2015, 332
Dev, 12/12/2008, 12/11/2014, 382
];
 
MaxMin:
Load Max(Date2) as Max,
Min(Date1) as Min
Resident Tab1;

Let varMinDate = num(Peek('Min',0,'MaxMin'));
Let varMaxDate = num(Peek('Max',0,'MaxMin'));

Drop table MaxMin;

MasterCalendar:
Load
Date($(varMinDate) + IterNo() - 1) as Date 
AutoGenerate 1
While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

Left Join(MasterCalendar)
Load Distinct Date,
Year(Date) as Year,
Month(Date) as Month
Resident MasterCalendar;

IntervalMatch:
IntervalMatch(Date)
LOAD
Date1,
Date2
Resident Tab1;

LEFT JOIN(IntervalMatch)
Load DATE_LINK,
Date1,
Date2
Resident Tab1;

Drop Fields Date1,Date2 From Tab1;