Databases Reference
In-Depth Information
This code creates a temporary table that contains the lowest and highest periods
from the
Main Data
table. As these values are originally in YYYYMM format, we
need to convert them to a date value by using the
Date#()
function.
The
Date#()
function essentially takes a string value
representing a date and converts it to an actual date
value based on the specified source format.
By using the
Min
and
Max
aggregations functions, the resulting fields will have the
very first and very last date, respectively, appearing in the source table.
Once the corresponding date value has been obtained, it is then converted to its
numerical representation (which is the number of days that have passed since
December 31, 1899) using the
Num()
function. For instance, December 28, 2011,
would be converted to 40905. This ensures that all of the dates can be treated as
consecutive numbers.
Using these two dates contained in the temporary table, we will generate a master
calendar that includes each month in them:
1.
Add the following code at the end of the previous
Load
statement:
//--- Assign the start and end dates to variables
LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');
LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');
DROP TABLE Temp_Calendar_Range; // Cleanup
Using the
LET
statement, we assign the lowest and highest dates to
temporary variables. The
Peek()
function is, which we will learn more
about in a later chapter, used to retrieve the values of these dates from
the
Temp_Calendar_Range
table. After creating the variable, we will no
longer need the
Temp_Calendar_Range
table, so it is deleted using the
DROP TABLE
statement.
