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]

Can you try this:

Tab1:

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

];

MaxMin:

Load Max(Date2) as Max,

  Min(Date1) as Min

Resident Tab1;

Let vMinDate = num(Peek('Min',0,'MaxMin'));

Let vMaxDate = 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 (Tab1)

IntervalMatch(Date)

LOAD Date1,

  Date2

Resident Tab1;

Data Model

I have not created rest of the calendar, but you can use the script give here to do the rest of the missing calendar

Creating A Master Calendar

[tschullo]

Thanks Sunny, but isnt that creating multiple records for each record in my fact table?

I was hoping to avoid that.

Tony

[sunny_talwar]

If you want, you can even keep the Interval Match table as a separate table as well (but it will form a synthetic key) which isn't a bad thing as mentioned by HIC here -> IntervalMatch

[sunny_talwar]

If I keep the Interval Match separate, this is what I get

[tschullo]

There is something wrong with the way you are getting the Min Max into the variables. As a result you are only getting a subset of the true full range of dates.  I can't figure out why it's not working, seems like it should.

[sunny_talwar]

I am not sure I follow you. What is the issue here?

[tschullo]

If you look at line 2 of the data, for Dev, the Date1 = 1/2/2001, but you are showing the min date to be 2/4/2006.

Your max date is 6/14/2012 but in the chart you can see that it should be 5/2/2012

[sunny_talwar]

My bad, forgot to updated the variables:

Tab1:

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

];

MaxMin:

Load Max(Date2) as Max,

  Min(Date1) as Min

Resident Tab1;

Let vMinDate = num(Peek('Min',0,'MaxMin'));

Let vMaxDate = num(Peek('Max',0,'MaxMin'));

Drop table MaxMin;

MasterCalendar:

LOAD Date($(vMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(vMinDate) + IterNo() -1 <= $(vMaxDate); 

IntervalMatch:

IntervalMatch(Date)

LOAD Date1,

  Date2

Resident Tab1;

[tschullo]

OK, figured it out: vMin.. vs varMin...

Let vMinDate = Num(Peek('Min',-1,'MaxMin'));
Let vMaxDate = Num(Peek('Max',-1,'MaxMin'));

//Drop table MaxMin;

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