Databases Reference
In-Depth Information
With the preceding script, we are simply adding the new field (
%Carrier
Group ID
) to the result of the
IntervalMatch
operation. This leaves us
with a table containing all possible combinations of
Interval
,
Date
,
Unique
Carrier Entity ID
, and the corresponding
%Carrier Group ID
value.
12. We will now end the transformation process by joining the expanded-intervals
table to the fact table so that the
%Carrier Group ID
field is added to it. Enter
the following script below the previous one:
Left Join ([Flight Data])
Load
Date,
[%Unique Carrier Entity Code],
[%Carrier Group ID]
Resident [Carrier Decode];
Drop Table [Carrier Decode];
Drop Field Date;
The
Join
operation is performed by matching both the
Date
and
%Unique
Carrier Entity Code
fields between the two tables. In the end, we issue a
Drop
statement to get rid of the
Carrier Decode
table since we don't need
it anymore. We also drop the
Date
field from the
Flight Data
table since it
was only needed during the
IntervalMatch
operation.
13. Now that the transformation has taken place and the new
%Carrier Group
ID
field has been added to the fact table, we can store the result into a new
QVD file and drop it from RAM with the following two statements:
Store [Flight Data] into [..\3.QVD\Transformed\Transformed -
Flight Data.qvd];
Drop Table [Flight Data];
Ordering, peeking, and matching all
at once
In the earlier sections, we have discussed three different functions commonly used in
data transformation. We will now present a use case in which all three functions will
complement each other to achieve a specific task.
