Databases Reference
In-Depth Information
As a quick recap, our main objective will be to add the
Carrier Group ID
ield
from the
Carrier Decode
table to the
Flight Data
table. When retrieving the ID
value for each of the records in the fact table, we must consider the date on which
the corresponding fact took place so that the correct value is assigned. Therefore, the
key between the fact table and the
Carrier Decode
table will be composed of a time
element (a
Date
field) and the
%Unique Carrier Entity Code
field, which exists in
both tables.
Let's follow these steps:
1. Create a new QlikView document; name it as
Transform - Carrier
Decode.qvw
and save it inside the
2.Workbooks
folder.
2. Go to the
Edit Script
window and add a new tab. From the
Rename Tab
dialog, type
Facts data
as the tab's name.
3. Once in the new tab, click on the
Table File…
button and browse to
the
Flight Data.qvd
file, located inside the
3.QVD\Source
folder.
4. After selecting the file and clicking on
Open
, click on
Finish
in the
File
Wizard
window to create the corresponding
Load
statement.
5. Name the table to be loaded as
Flight Data
by typing it before the
Load
keyword and enclosing it within square brackets. Don't forget the colon at
the end of the name.
6. Now we will create a new calculated field to build a date representation of
the
Period
field. Use the following expression to create the new field:
Date#(Period, 'YYYYMM') as Date
7. We will rename the original
%Carrier Group ID
field from the
Flight Data
table to
OLD_Carrier Group ID
, so that we can use the new field resulting
from the transformation instead.
8. The rest of the script will not be modified, so our
Load
statement should be:
[Flight Data]:
LOAD
Date#(Period, 'YYYYMM') as Date ,
[%Carrier Group ID] as [OLD_Carrier Group ID],
[%Airline ID],
[%Unique Carrier Code],
[%Unique Carrier Entity Code],
[%Region Code],
[%Origin Airport ID],
[%Origin Airport Sequence ID],
[%Origin Airport Market ID],
[%Origin World Area Code],
[%Destination Airport ID],
[%Destination Airport Sequence ID],
