QlikView - Synthetic Key

Advertisements

Previous Page
Next Page  

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 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.

synthetic_key_load_script

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.

synthetic_key_data_model

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 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.

Advertisements