Databases Reference
In-Depth Information
2.
The next step is adding the code that will create the actual master calendar
table. Enter the following below the
Drop Table
statement:
[Master Calendar]:
LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date, 'YYYY-MM') as [Year - Month],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
;
LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
//--- Remove the temporary variables
LET vMinDate = Null();
LET vMaxDate = Null();
You may notice that the first
LOAD
statement in the preceding script is
missing a source. When no source is specified, QlikView uses the result
of the next
LOAD
statement as the source. This is called a
Preceding Load
.
The script first creates a table with a single column called
Temp_Date
. By
using the
AUTOGENERATE (1) WHILE $(vMinDate) + IterNo() - 1 <=
$(vMaxDate)
statement, QlikView iterates over each day between the lowest
(
vMinDate
) and highest (
vMaxDate
) period and creates one record per day.
By applying the
MonthStart()
function, a table containing every first day
of the month for the intermediate period is created. The preceding load
statement then loads the
Temp_Date
table and applies various date functions
to it to create the final
Master Calendar
table. At this point, we also use an
expression to create the dimension key
Period
by concatenating the year
and month into a single number. For example, October 2011 will be stored as
201110. The
Period
field will then be used to associate the
Master Calendar
table and the fact table.
After the master calendar is created, the temporary variables are deleted by
setting their value to
Null()
.
