QlikView Master Calendar - Multiple Dates

[#1]

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; 

[Clever_Anjos]

You need this: Canonical Date

[#3]

I was able to fix the issue. I had a bug in the code. Thanks anyway.

[jonas_rezende · Accepted solution]

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!