Master Calendar not associating date data

[jason_nicholas]

I am using a standard master calendar script (I will comment the exact script below). However, I am also using my load script to handle some date-related transformations, and I probably need to consolidate all of them to the master calendar. I have not been able to successfully do this while retaining data accuracy, and I would appreciate any guidance.

In my fact tables (AV_Support), I load:

[Booking Date],
Year([Booking Date]) as Year,
Month([Booking Date]) as Month,
NUM(Month([Booking Date])) as NumMonth,
Day([Booking Date]) as Day,

I have a second table that loads the same way (Live_Streaming), using the same date functionality. This generates a synthetic key, and I am trying to use the Master Calendar to manage date functions to eliminate this problem. So far, I haven't gotten this to work properly.

Here are the issues I have been encountering:

By creating Year, Month, and Day in both the fact tables and the master calendar, I get multiple synthetic keys

By removing the [Year], [Month] ,and [Day] from the fact tables, The Master Calendar no longer links with the rest of the data, the synthetic table only associates [Reservation ID] and [Booking Date] (and $Syn 2 and $Syn3 are removed). Any charts which use [Month] as a dimension no longer calculate correctly, using [Month] from the Master Calendar

By removing [Year], [Month], and [Day] from the master calendar, the synthetic table also includes [Year], [Month], and [Day], the Master Calendar remains disconnected, and the flags generated in the calendar script (below) do not associate with [Booking Date], and therefore do not impact my charts.

Does the synthetic key in the image above have the potential for negative affects on my data? If I am concerned over a non-issue, then my data seems to work best with it included.

If synthetic keys are a problem, what is the best way to get rid of them?

[jason_nicholas]

Master Calendar script used:

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

TempMinMaxDate:
Load
Min([Booking Date]) as MinDate,
Max([Booking Date]) as MaxDate
Resident AV_Support;
 
Let varMinDate = Num(Peek('MinDate', 0, 'TempMinMaxDate')); 
Let varMaxDate = Num(Peek('MaxDate', 0, 'TempMinMaxDate')); 
DROP Table TempMinMaxDate; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 
Load 
TempDate AS OrderDate, 
week(TempDate) As Week, 
Year(TempDate) As Year, 
   Month(TempDate) As Month,
   Day(TempDate) As Day, 
   YeartoDate(TempDate)*-1 as [YTD Flag], 
   YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
IF(Year([TempDate])=Year(Today()) or (NUM(Month([TempDate]))= 12 and Year([TempDate])=Year(Today()-1) and Month(Today())=1),1,0) as [Current Year Flag],
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
   date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

[sunny_talwar · Accepted solution]

Do you want Booking Date or OrderDate?

MasterCalendar: 
Load 
TempDate AS [Booking Date], 
week(TempDate) As Week, 
Year(TempDate) As Year, 
  Month(TempDate) As Month,
  Day(TempDate) As Day, 
  YeartoDate(TempDate)*-1 as [YTD Flag], 
  YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
IF(Year([TempDate])=Year(Today()) or (NUM(Month([TempDate]))= 12 and Year([TempDate])=Year(Today()-1) and Month(Today())=1),1,0) as[Current Year Flag],
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
  date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar;

and don't create Month, Year in Fact table

[jason_nicholas]

Booking Date is correct. It was much simpler than I was making it, and the solution was obvious now that I see it. thanks for that.

I have two follow up questions:

When I change make the change above, I am left with one remaining synthetic table. this is because both the [Booking Date] and [Reservation ID] are keyed between the two tables. The [Booking Date] needs to be keyed because of the functionality of the calendar flags. [Reservation ID] is the only way I have to identify an association between the two tables. Is this synthetic table an issue?

The second follow-up question:

I took the master calendar script from a thread in this community, and made some small adjustments for my purposes. There is a function in the script that I do not understand. Could you please clarify what this accomplishes?

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

[sunny_talwar]

1) It is synthetic table... read more on this here: Synthetic Keys

2) read about InYear here: https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/i...

[jason_nicholas]

Thanks. There is a lot of information out there stating the need to remove synthetic keys, but it seems like my use case isn't likely to cause an issue. Since I have been able to remove all of the rest of them throughout the load, I won't worry two much about this single synthetic table.

Regarding InYear, I have read that page but it doesn't really explain (in inexperienced terms) what this piece of the script is doing.

Returning true if the record date is within the same year as the date referencing the start of the month of the max date, offset by one year?

So, if my max date is in January 2018, then RC12 will show true if the record date is in 2017. But if my max date is in December 2017, this same function will return true if the record date is in 2016?

[sunny_talwar]

I think the best way to see what it is doing is to create a sample...

Table:

LOAD Date,

  InYear(Date, Today(), -1) as FlagLY,

  InYear(Date, Today(), 0) as FlagCY;

LOAD Date(MakeDate(2015, 12, 31) + IterNo()) as Date

AutoGenerate 1

While IterNo() <= 731;

I created dates for two years (2016 and 2017) and based on Today, I used InYear() function. InYear(Date, Today(), -1) flagged all the year 2016 with -1 and year 2017 as 0. If I used InYear(Date, Today(), 0), it flagged the complete 2017 with -1 and flagged 2016 as 0.

So, basically the InYear function checks if date falls in the same year as Today() (when the third parameter is 0) or one year prior to today's year (when the third paramter is -1).

Does this help?

[jason_nicholas]

This helps somewhat. It confirms that I understand the basic syntax. But I don't get what it is intended to do in the script I am using.

It seems like the function, in the case of the master calendar, would do something differently based solely on the last date record I have. I don't understand the benefit of this.

My specific data happens to have one entry for January of 2018 already, so that means this works as a current year flag (which I already have in a different format). But if my data didn't happen to have that January entry already, it would work as a previous year flag (which I also already have).

So is [RC12] simply extraneous since I have [Current Year Flag], [LastYTDFlag] and [YTDFlag]? Or does it have another purpose/function I am not seeing? I could easily clear it, but since it was added to a very basic template, I am trying to understand the full value of it before discarding.