Databases Reference
In-Depth Information
9. Now, let's create a new tab by clicking on the
Add new tab
button on the
toolbar. The new tab will be named
Intervals
.
10. In this new tab, we will enter the following script:
[Carrier Decode]:
IntervalMatch (Date, [%Unique Carrier Entity Code])
LOAD
[Start Date],
If(Len([End Date]) < 1, Today(1), [End Date]) as [End Date],
[Unique Carrier Entity] as [%Unique Carrier Entity Code]
FROM
[..\3.QVD\Source\Carrier Decode.qvd]
(qvd);
With the preceding script, a new table is being created as the result of the
IntervalMatch
operation. In this case, we are using the extended syntax of
the function so that the resulting table has one record for each combination
of interval (
Start Date
and
End Date
), data point (
Date
), and dimension
(
%Unique Carrier Entity Code
) value.
When using the extended syntax, all fields specified as the
function's parameter must exist in the previouslyloaded
fact table, as well as listed in the
Load
statement to which
it is being applied.
We are also ensuring that all intervals are closed, which is a requirement of
the
IntervalMatch
function, by using a conditional expression. Whenever
an open interval is encountered, the date of when the script is executed will
be set as the
End Date
ield for that interval.
11. Now that we have expanded the intervals, let's associate the dimension value
we are interested in (
%Carrier Group ID
) so that we can incorporate it into
the fact table. Do this by entering the following code below the previous one:
Inner Join ([Carrier Decode])
LOAD
[Start Date],
If(Len([End Date]) < 1, Today(1), [End Date]) as [End Date],
[Unique Carrier Entity] as [%Unique Carrier Entity Code],
[Carrier Group ID] as [%Carrier Group ID]
FROM
[..\3.QVD\Source\Carrier Decode.qvd]
(qvd);
