TLDR: A new Qlik Sense training video uses a tired old-school Master Calendar script. I propose that updated training materials should use an updated script.

I just watched  a new  video by Michael Tarallo of Qlik titled “Understanding the Master Calendar – Qlik Sense and QlikView“.  Before I  pick on the contents of this particular video, I want to  credit  Michael for  producing many excellent training videos that are worth watching and learning from. I  highly recommend them.

The video does a great job of explaining the need for and function of a Master Calendar in your data model. It then goes on to show an actual script.

I can’t discuss Master Calendar without expressing disappointment that Calendar generation is not yet a builtin function in Sense. Something like QlikView Components (QVC) does with the single script line:

CALL Qvc.CalendarFromField('OrderDate');

On to the script used in this new video. I’ll reproduce the entire script below and then comment on the techniques used and suggest some more “modern” approaches.

The video script is  similar to the script used in the current QlikView Developer course . I acknowledge that this script works as is and produces correct results. But I don’t think it should be taught to newbies as good scripting. Here’s the script from the video:

Obsolete Code

1. Why is this field created  and where is it used?

It’s not used. It’s left over from a very old version of the exercise and it doesn’t serve any purpose.

2. Why are we sorting the table? Is this statement useful?

Even if I could think of a good reason why  the Calendar table should be in order, it already is in this order because the TempCalendar was generated in a loop. Statement unnecessary.

Inefficient Code

Loading a Resident table can be very slow for a large table.

 

Experienced scripters use the FieldValues array instead.

What’s the difference? FieldValues only reads the distinct values of a field — maybe a thousand or so for several years of dates. Resident reads every row of the table. For a 10M row fact table, that’s 10M reads and the time scales up linearly. The difference can be dramatic.

Error Prone and Extra Work

Peek(), used on lines 13 & 14,  is one of those functions that fails silently. That is, if you misspell a field or table,  you won’t get a script error. Misspelling a variable will also not generate  a script error. Maybe. Or maybe not. Or maybe you will get a Calendar that starts in year 1899.  Depends on which line you make the spelling error on. If your misspelling does result in a script syntax error, it will be downstream from where you created the problem. There are multiple ways to mess this one up and some very curious potential results.

Don’t forget to DROP those temp tables on lines 15 and 35.

And those varMinDate and varMaxDate variables really should be cleared as well.

You can avoid all the syntax traps and extra cleanup by coding this a as a Preceding Load.  Here’s the same script written as a Preceding Load:

Nothing to remember (or forget) to clean up.  If you misspell a fieldname, you will get an understandable error at the right place. This is the calendar script I wish we would provide to newcomers.

Of course if you’ve attended the Masters Summit for QlikView, you’ve learned all about FieldValues, Preceding Loads and more. If not, attend a summit this Fall in NYC or Copenhagen.

QVC users don’t even get out of bed to generate Calendars. But they know that all that best practice stuff is happening under the covers. If you want to learn more about QVC, join my online class June 4 or a future class at Q-on.bi.

-Rob

Entire script suitable for copying:

MasterCalendar: 
Load 
 TempDate AS OrderDate, 
 week(TempDate) As Week, 
 Year(TempDate) As Year, 
 Month(TempDate) As Month, 
 Day(TempDate) As Day, 
 'Q' & ceil(month(TempDate) / 3) AS Quarter, 
 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
 WeekDay(TempDate) as WeekDay 
;

//=== Generate a temp table of dates === 
LOAD 
 date(mindate + IterNo()) AS TempDate
 ,maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
 min(FieldValue('OrderDate', recno()))-1 as mindate,
 max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');