A Synthetic Key is QlikView’s solution to create an artificial key when there is ambiguity about which key to use between two tables. This situation arises when two tables have two or more fields in common. QlikView’s feature of creating an association in memory automatically detects this scenario and creates an additional table, which will hold the value of the new key created.
Input Data
Let us consider the following two CSV data files, which are used as input for further illustrations.
Sales:
ProductID,ProductCategory,Country,SaleAmount
1,Outdoor Recreation,Italy,4579
2,Clothing,USA,4125
3,Costumes & Accessories,South Korea,6521
Product:
ProductID, Country
3,Brazil
3,China
2,Korea
1,USA
Load Script
We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file.
Data Model
Next, we look at the data model by using the menu command for table viewer, Control+T. The following screen comes up, which shows the creation of a third table that supplies the value of the synthetic key as both the tables have ProductID and Country as matching keys.
Impact of Synthetic key
Synthetic keys indicate the flaw in the data model that is being used. They do not cause any issue in the correctness of the data or the performance of the report. Things will work fine if a big data model has one or two instances of synthetic keys. However, if we have too many of them, then that is an implication to redesign the data model.
