Databases Reference
In-Depth Information
The following changes were made:
• By adding the
LEFT JOIN ([Aircraft Types])
statement, we tell QlikView
not to load the data from the
Aircraft_Group.csv
file to a separate table.
Instead, it will be joined to the table specified between the parentheses. A join
is made over the common fields between both tables, in this case
[%Aircraft
Group Type]
.
• By adding the
CONCATENATE ([Aircraft Types])
statement, we tell
Qlikview not to load the data from the
Aircraft_2010_Update.csv
file to a
separate table. Instead, the rows are appended to the table specified between
the parentheses. Fields that are not shared between tables, for example, the
ield
[%Aircraft Group Type]
, get null values for the rows that are missing
this field.
• The
AC_GROUPNAME
column contains both the
Engine Type
and
Number of
Engines
fields, separated by a comma. The
SubField(AC_GROUPNAME, ',',
1) as [Engine Type],
expression uses the
SubField
function to split the
AC_GROUPNAME
string into subfields based on the
','
delimiter. The first
subfield returns the
Aircraft Engine Type
table, the second subfield returns
the
Aircraft Number of Engines
table.
• As we no longer require the
[%Aircraft Group Type]
key field, the
DROP
FIELD [%Aircraft Group Type] FROM [Aircraft Types];
statement is
used to remove it from the
Aircraft Types
table.
To see the effect of our changes, let's reload the script by selecting
File
|
Reload
from
the menu, or by pressing
Ctrl
+
R
.
After reloading has finished, open the
Table Viewer
window by selecting
File
|
Table Viewer
from the menu, or by pressing
Ctrl
+
T
.
As we can see, all the source tables have been merged into a single
Aircraft Types
dimension table.
