Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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
Thanks Sunny, but isnt that creating multiple records for each record in my fact table?
I was hoping to avoid that.
Tony
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
If I keep the Interval Match separate, this is what I get
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.
I am not sure I follow you. What is the issue here?
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
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;
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);