One of the standard pieces of script that tends to be used over and over again is the creation of a Master Calendar table. The first stage of this is to find the date range, from minimum to maximum, from your fact table and create all the possible days in between. In fact, this is even part of the main QlikView Developers' training course. We looked at this in Chapter 5, The Right Data Model Pays Dividends, when we created our Data Island.
The normal way to find the minimum and maximum dates is to use a piece of script such as this:
TempDates:
LOAD Min([Order Date]) as MinDate,
Max([Order Date]) as MaxDate
Resident SalesOrders;
LET varMinDate=peek('MinDate',0,'TempDates');
LET varMaxDate=peek('MaxDate',0,'TempDates');
DROP Table TempDates;Here, we created a one record table with the minimum and maximum values as separate fields and then used Peek on the resulting table to get the values.
This is fine if we only have a couple of thousand rows; however, when we talk...