The 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 tables as shown below. Here we cannot get the value of the 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.
Table Chart
On completing the above steps and creating a Table box to view the data, we get the country columns along with other columns from the Sales table.
