QlikView - Mapping Tables

Mapping table is a table, which is created to map the column values between two tables. It is also called a Lookup table, which is only used to look for a related value from some other table.

Input Data

Let us consider the following input data file, which represents the sales values in different regions.

ProductID,ProductCategory,Region,SaleAmount
1,Outdoor Recreation,Europe,4579
2,Clothing,Europe,4125
3,Costumes & Accessories,South Asia,6521
4,Athletics,South Asia,4125
5,Personal Care,Australia,5124
6,Arts & Entertainment,North AMerica,1245
7,Hardware,South America,456
8,Home & Garden,South America,241
9,Food,South Asia,1247
10,Home & Garden,South Asia,5462
11,Office Supplies,Australia,577

The following data represents the countries and their regions.

Region,Country
Europe,Germany
Europe,Italy
South Asia,Singapore
South Asia,Korea
North AMerica,USA
South America,Brazil
South America,Peru
South Asia,China
South Asia,Sri Lanka

Load Script

The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from the Data from Files tab and browse for the file containing the above data. Click OK and ess Control+R to load the data into the QlikView's memory.

Create Table Box

Let us create two table boxes for each of the above table as shown below. Here we cannot get the value of country in the Sales region report.

Create the Mapping Table

The following script produces the mapping table, which maps the region value from the sales table with the country value from the MapCountryRegion table.