How The Synthetic Keys Are Generated In Qlikview

  • Qlikview Tutorial
  • QlikView Data Loading
  • QlikView Report Interface
  • QlikView Data Transformation
  • QlikView Data Model
  • QlikView Data Analysis
  • Qlikview Useful Resources
  • Selected Reading

Key to generating a bell curve. Synthetic keys. When two or more data tables have two or more fields in common, this suggests a composite key relationship. Qlik Sense handles this by creating synthetic keys automatically. These keys are anonymous fields that represent all occurring combinations of the composite key. Synthetic keys in Qlikview: ‘Synthetic keys occur when two or more tables have two or more fields in common.’ However, when we have more than one common field between two or more tables, QlikView creates “SYNTHETIC KEYS” and “SYNTHETIC TABLE”.


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 memoryautomatically detects this scenario and creates an additional table, which will hold the value of the new key created.

Input Data

This is the synthetic table which stores the combination of values for the two fields which, form the synthetic key. The synthetic keys in a data model can cause the application to have slow response time and sometimes even consume all available resources. Therefore, need to be avoided when possible. Aug 14, 2017  In this session we have discussed regarding Synthetic Keys & Circular Reference in Qlikview. Dealing with Synthetic Keys & Circular Reference in Qlikview # Part 16. 98 What is Sythentic Key. Mar 07, 2019 Also, for QlikView data structure optimization, a formation of synthetic keys must avoid. Synthetic keys create when there are one or more common fields between two or more tables. So, this was all about QlikView Data Structure Tutorial. Hope you like our explanation.

Let us consider the following two CSV data files, which are used as input for further illustrations.

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

Generated

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 in qlik sense

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.

  • Qlikview Tutorial
  • QlikView Data Loading

How The Synthetic Keys Are Generated In Qlikview One

  • QlikView Report Interface
  • QlikView Data Transformation
  • QlikView Data Model
  • QlikView Data Analysis
  • Qlikview Useful Resources

How The Synthetic Keys Are Generated In Qlikview 1

  • Selected Reading

Many times, we need some data to be generated programmatically by the software being used, which is not coming from a source. For example, 100 random numbers or just the dates of 23rd week of a year. A data analyst may need such data to be created to performsome analysis on the data that does not contain these values as it arrived. QlikView provides a function called Autogenerate, which can be used for such requirement.

Data Requirement

Consider a scenario where we need to find only the dates, which are a Thursday or a Sunday. We need to find it for the range starting today until the end of the year. We create the following script, which will achieve this.

Explanation

We declare two variables to capture the first day of the current month and end of the year. Next we apply various functions and a filter condition to generate the required values. The recno() function creates one record for each of these dates. We add Autogenerate function giving the variables as the range.

Generated data

On loading the above script to QlikView's memory and creating a Table Box using the menu Layout → New Sheet Objects → Table Box, we get the data created as shown below.