QlikView Master Calendar - Multiple Dates

Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: QlikView Master Calendar - Multiple Dates

Clever_Anjos — Mon, 09 May 2016 20:15:48 GMT

You need this: Canonical Date

Re: QlikView Master Calendar - Multiple Dates

Mon, 09 May 2016 20:20:17 GMT

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

Re: QlikView Master Calendar - Multiple Dates

jonas_rezende — Tue, 10 May 2016 11:53:22 GMT

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!

topic QlikView Master Calendar - Multiple Dates in QlikView

QlikView Master Calendar - Multiple Dates

Re: QlikView Master Calendar - Multiple Dates

Re: QlikView Master Calendar - Multiple Dates

Re: QlikView Master Calendar - Multiple Dates