Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Morning:
How do I use Master Calendar if I have multiple dates? I am trying to build a star schema. I have two different types of dates - OptInDate from Table: Incident and LOA_Date from Table: CloseRate - Screen Shot below. I used the master calendar script twice times. I created two calendars - OptInMasterCalendar and SLOA_MasterCalendar. The OptInMasterCalendar is connected to the Incident table which is correct. But SLOA_MasterCalendar must be connected to CloseRate table. It is currently not. How can I fix this problem. I am pasting the screen shot and the script. I would appreciate your help.
//OptInDate
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OptInDate) as minDate,
max(OptInDate) as maxDate
Resident Incident;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
OptInMasterCalendar:
Load
TempDate AS COptInDate,
week(TempDate) As OptInWeek,
Year(TempDate) As OptInYear,
Month(TempDate) As OptInMonth,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as OptInCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as OptInLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as OptInRC12,
date(monthstart(TempDate), 'MMM-YYYY') as OptInMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as OptInQuarter,
//Concat(QuarterName(TempDate)," ",Year(TempDate)) AS OptInQuarterYear,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as OptInWeekYear,
WeekDay(TempDate) as OptInWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
//Show LOA Date
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(Show_LOA_Date) as minDate,
max(Show_LOA_Date) as maxDate
Resident CloseRate;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
SLOA_MasterCalendar:
Load
TempDate AS SLOA_Date,
week(TempDate) As SLOA_Week,
Year(TempDate) As SLOA_Year,
Month(TempDate) As SLOA_Month,
Day(TempDate) As SLOA_Day,
YeartoDate(TempDate)*-1 as SLOA_CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as SLOA_LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as SLOA_RC12,
date(monthstart(TempDate), 'MMM-YYYY') as SLOA_MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as SLOA_Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as SLOA_WeekYear,
WeekDay(TempDate) as SLOA_WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Hi, Santoshi Reddy.
What's the date of the field Close Rate table that your content is equal to the SLOA_MasterCalendar? It is this field that will link the tables.
Hope this helps!
You need this: Canonical Date
I was able to fix the issue. I had a bug in the code. Thanks anyway.
Hi, Santoshi Reddy.
What's the date of the field Close Rate table that your content is equal to the SLOA_MasterCalendar? It is this field that will link the tables.
Hope this helps!