Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Not applicable

QlikView Master Calendar - Multiple Dates

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; 

1 Solution

Accepted Solutions
Highlighted
jonas_rezende
Valued Contributor

Re: QlikView Master Calendar - Multiple Dates

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!

View solution in original post

3 Replies
Highlighted
Employee
Employee

Re: QlikView Master Calendar - Multiple Dates

You need this: Canonical Date

Highlighted
Not applicable

Re: QlikView Master Calendar - Multiple Dates

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

Highlighted
jonas_rezende
Valued Contributor

Re: QlikView Master Calendar - Multiple Dates

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!

View solution in original post