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.
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
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.
On completing the above steps and creating a Table box to view the data, we get the country columns along with other columns from Sales table.