Databases Reference
In-Depth Information
Using MAPPING tables
The
MAPPING
statement provides an alternative to the
JOIN
statement in a very
specific scenario: when you want to replace a single key value with a value from
a lookup (mapping) table. To see how this works, let's enrich our
Aircraft Types
dimension table by adding the manufacturer's country. To do this, we open up the
script editor and follow these steps:
1. Place the cursor directly above the
LOAD
statement for
[Aircraft Types]
.
2. Click the
Table Files
button in the tool pane and navigate to the
Data
Files\CSVs
folder.
3. Select the file
Aircraft_Manufacturers.csv
.
4. Set the
Labels
drop-down list to
Embedded Labels
.
5. Complete the
Table File Wizard
by clicking on
Finish
.
6. Replace the
Directory;
text with
Map_Manufacturer_Country:
to assign that
name to the table.
7. On the next line, prefix
MAPPING
to the
LOAD
statement.
8.
Now add a line below the line
MANUFACTURER as [Aircraft
Manufacturer],
in the
[Aircraft Types] LOAD
statement.
9.
On this line add the following script:
ApplyMap('Map_Manufacturer_
Country', MANUFACTURER, 'Unknown') as [Aircraft Manufacturer
Country],
.
10. Add a line below the line
MANUFACTURER as [Aircraft Manufacturer],
in
the
CONCATENATE([Aircraft Types]) LOAD
statement.
11. On this line add the following script:
ApplyMap('Map_Manufacturer_
Country', MANUFACTURER, 'Unknown') as [Aircraft Manufacturer
Country],
.
The modified script for the mapping table should look as follows:
Map_Manufacturer_Country:
MAPPING LOAD Company,
Country
FROM
[..\Data Files\CSVs\Aircraft_Manufacturers.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
