Error on Master Calendar creation.

[avv]

Hello,

I´m trainning with Data Modeling with Qlik Cloud Analytics / Qlik Sense/Creating the Master Calendar

in Qlik Learning and i get this error message when i try to create the previous step to Master Calendar Statement.

I can´t find the error, because code is copied from the learning course steps, ¿Could someone tell me where is the error? The full cod section is below:

tFirstAndLastDates:
LOAD
Num(Min(FieldValue('OrderDate', RecNo()))) AS MinDate,
Num(Max(FieldValue('OrderDate', RecNo()))) AS MaxDate
AUTOGENERATE FieldValueCount('OrderDate');

LET vMinDate = Num(Peek('MinDate', 0, 'tFirstAndLastDates'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'tFirstAndLastDates'));
//LET vToday = Peek('maxDate', 0, 'tFirstAndLastDates');

//TRACE vMinDate = $(vMinDate) => $(=Date($(vMinDate)));
//TRACE vMaxDate = $(vMaxDate) => $(=Date($(vMaxDate)));

TempCal:
LOAD
DATE($(vMinDate) + IterNo() -1) AS TempDate
RESIDENT tFirstAndLastDates
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

MasterCalendar:
LOAD *,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) as FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(OrderDate), 'MMM')), FiscalMonth) as FiscalMonthName; // Fiscal Calendar Month Name
LOAD
TempDate AS OrderDate, // Model links here to Orders
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
WeekDay(TempDate) AS WeekDay,
Dual('Q' & Ceil(Month(TempDate) / 3), Ceil(Month(TempDate) / 3)) AS Quarter,
Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate) & '-' &
If(Week(TempDate)=1 AND Month(TempDate)=12,Year(TempDate)+1,
If(Match(Week(TempDate), 52, 53) AND
Month(TempDate)=1, Year(TempDate)-1, Year(TempDate))) AS WeekYear,
InYearToDate(TempDate, $(vToday), 0) * -1 AS CYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 as LYTDFlag,
If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)), 1, 0 ) AS IsInYTD,
If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)), 1, 0) AS IsInQTD,
If(Day(TempDate) <= Day($(vToday)), 1, 0) AS IsInMTD,
If(Month(TempDate) = Month($(vToday)), 1, 0) AS IsCurrentMonth,
If(Month(AddMonths(TempDate,1)) = Month($(vToday)), 1, 0) AS IsLastMonth,
Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12) + 1 as FiscalMonth, // Fiscal Calendar Month
YearName(TempDate, 0, $(vFiscalYearStartMonth))as FiscalYear // Fiscal Calendar Year

RESIDENT MasterCalendarTemp;

//DROP TABLE MinMaxTemp;
//DROP TABLE MasterCalendarTemp;

exit script

Thanks!

Creating the Master Calendar

[marcus_sommer]

The called variables vMinDate and vMaxDate are not existing. This might be caused if the underlying OrderDate isn't a date else a string which would force the num(min()) to NULL and assigning NULL to a variable deletes it.

Further if an equally table to tFirstAndLastDates is already there this table won't be created else the load-content per auto-concatenate added to the previous table and this results then in NULL for the peek() query.

Also any qualifying or ALIAS in beforehand may change the field-names and the peek() results again in NULL.

[rwunderlich · Accepted solution]

Since you are learning you probably want to finish debugging this exercise. But in regards to Master Calendar creation, i want to point out this particular script pattern -- peeking the min/max values into temp variables -- has long been considered a poor practice. Here's why and a much easier pattern https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

-Rob