The Fastest Dynamic Calendar Script (Ever)

[tseebach]

Me and a colleague have put together a new Calendar, combining a super fast method for generating the needed calendar, while also removing a lot configuration options in the previous methods.

So if you're using a  Min/Max based approach like the one described here: Creating A Master Calendar

Then you're properly used to waiting for the Min/Max to find the largest values in your dataset. This can take minutes. What we have here takes less than a second. I've actually not been able to time this, because this calendar script takes less than a 1 seconds even on billions of rows on decent hardware. The only exception to this is if you use a date range starting before christ and spans a thousand years. If you have a date range, with a lot of holes in it (dates with no data), then you should use the last parameter:

Call Calendar('[Date (NK)]','MyCal','Pre.','','true');

The Attached qvw shows both methods (remember the qvs file).

I've not commented it, because most people will not grasp what is going on anyway, and don't need to

To try it out, include the following in your script, and add this to your code:

Call Calendar('DateField');

/* ************************************************************************************

itelligence Standard Qlik Calender Torben Seebach/Martin Didriksen Special Thanks to Rob Wunderlich and John Witherspoon

Configure Calender parameters

  Syntax:

       Calendar(DateField[,CalendarName][,Prefix[,Suffix[,FullCalendar]]])

  Example:

       Call Calendar('Date (NK)','MyCal','Pre.','','true'); // creates a the MyCal table and fields are prefixed with Pre. In the superfast variat

       Call Calendar('Date (NK)'); //creates a table called calendar based of "Date (NK)" field

  Mandatory:

       @_DateField Contains the name of the field to connect the calendar to

  Optional:

       @_CalendarName Contains the name of the calendar we create      
       @_CalendarPrefix A Prefix for all fields
       @_CalendarSuffix A Suffix for all fields      
       @_FullCalendar If this contains a value then it creates a calendar with all dates from Min(Date) to Max(Date), default is the slowest option

************************************************************************************ */

Sub Calendar (_DateField,_CalendarName,_CalendarPrefix,_CalendarSuffix,_FullCalendar)

  Let _StartTime = Now(); 
  Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');
  Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');
  Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');
  Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);
  Let _DateField = PurgeChar(_DateField,'"[]');

  "$(_CalendarName)":
   LOAD
        Distinct [$(_DateField)] as [$(_DateField)],
        Text(Date([$(_DateField)])) as [$(_CalendarPrefix)DateText$(_CalendarSuffix)],
        Year([$(_DateField)]) as [$(_CalendarPrefix)Year$(_CalendarSuffix)],
        Week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)WeekYear$(_CalendarSuffix)],
        Week([$(_DateField)]) as [$(_CalendarPrefix)Week$(_CalendarSuffix)],
        Month([$(_DateField)]) as [$(_CalendarPrefix)Month$(_CalendarSuffix)],
        Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)YearMonth$(_CalendarSuffix)],         'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Quarter$(_CalendarSuffix)],
        AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)MonthSerial$(_CalendarSuffix)],
        AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)QuarterSerial$(_CalendarSuffix)],

        AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial') as [$(_CalendarPrefix)WeekSerial$(_CalendarSuffix)] ;

  If _FullCalendar=1 Then
        LOAD Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [$(_DateField)]
               While (_DateStart+(Iterno()-1)<=_DateStop);
        LOAD
               Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,
               Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop
        AUTOGENERATE FieldValueCount('$(_DateField)');
Else
        LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]
        AUTOGENERATE FieldValueCount('$(_DateField)');
End If

  /* clean variables  */

Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
Let _StartTime = NULL;
Let _vDateStart = NULL;
Let _vDateStop = NULL;
Trace $(_CalendarName) created in: $(_TotalTime) seconds;
Let _TotalTime = NULL;

ENDSUB

Updated 2014-05-23:

• Implement preceding load method suggested by Rob Wunderlich
• Fixed Timer to show seconds
• Corrected error in documentation
• Added PurgeChar so [Date A],"Date B" also works.
• Thanks to Stephen Redmond for pointing to a boolean error in line 22

Other than my brilliant Collegue Martin Didriksen, I also want to give John Witherspoon some credit for inspiration on the autogerate method used. I think he was the first who posted it online.

[Anonymous]

Thanks for the script routine. This is a nice way to package a standard calendar.

....a bit overboard on the script speed promotion spiel.

After testing, I noticed a few things.

1) the trace event on the variables you included to try and measure execution time are (among other things) not accurate. I loaded a single, autogenerated date field into a table to create the calendar 'connection' and used my start date as Jan 1st, 1510. This generates approx. 185K rows in about 1 sec. Then I added you sub routine. The total script execution increased to about 6 sec. However, the trace recorded 0.00005787037 seconds.

2) in my experience, the calendar script is typically the least expensive in load script performance. That said, I think there is something to be said for simplicity.

[rwunderlich]

Another simplification is to move the min/max to the bottom of the preceding load. That will eliminate the Temp table and variables. Less to get wrong.

[tseebach]

I agree, its not measured in seconds but days so it should be multiplied by 24*60*60.

And if you have an obscure date range, like 0 to 999999 it creates a lot of rows. But in that case, you should use the _FullCalendar option, and only create records that have dates.

[tseebach]

Rob, I've been thinking it might be possible, but I could not get my head around how exactly to do it?

[rwunderlich]

Here's the "Classic Developer I' training calendar adapted as a preceding load as an example.

MasterCalendar:

LOAD

  TempDate AS Date,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Weekday(TempDate) AS WeekDay,

  inyeartodate(TempDate, maxdate, 0) * -1 AS CurYTDFlag,

  inyeartodate(TempDate, maxdate, -1) * -1 AS LastYTDFlag

;

//=== 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 fact table ===/

LOAD

  min(Date)-1 as mindate,

  max(Date) as maxdate

RESIDENT Facts

;

[Anonymous]

Torben Seebach wrote:

I agree, its not measured in seconds but days so it should be multiplied by 24*60*60.

The load script dialog includes the word "seconds"......?......although if I calculate the value as you mentioned it comes to about 5 which would be correct, I believe.

Torben Seebach wrote:

And if you have an obscure date range, like 0 to 999999 it creates a lot of rows. But in that case, you should use the _FullCalendar option, and only create records that have dates.

I'm not sure what you mean by this......

[tseebach]

Clever, I did not know I could use a while in the secondary preceding load... I will adapt and put it up soon... Guess I just got back to school...

[tseebach]

I'm not sure what you mean by this......

What I mean is that if you have the following dates 2014-01-02, 2014-01-04 and 2014-01-06, then its not always that you wish your calendar should contain all the dates in between (2014-01-03, 2014-01-05). The final parameter controls that:

Call Calendar('[Date (NK)]','MyCal','Pre.','','true');

[tseebach]

Rob, I've added the brilliant preceding while load piece you posted. And credited you Thanks.

[tseebach]

Autopilot, I've fixed the timing now in seconds and changed my wording a bit. Thanks.