Synthetic keys in QlikView

In QlikView, the common fields of two input files are associated automatically by creating a separate third table called $Syn1. This synthetic table stores the identified common fields and thus forms a synthetic key. Let’s see what is a synthetic key, Do they affect the data model? And why should we remove synthetic keys?

In this blog, we will discuss the following topics:

What is a Synthetic key in QlikView? Why should we remove Synthetic keys? How to create a Synthetic key in QlikView? How to resolve the Synthetic keys? Need for Removing Synthetic Keys Conclusion

In this blog, we will discuss the following topics:                      

What is a Synthetic key in QlikView?

The Synthetic key is created when two or more tables share more than one common field that signifies a composite key connection between the tables. The QlikView handles composite keys by generating Synthetic keys automatically. These keys represent all occurred combinations of composite keys. 

When data is loading, if you receive a warning message regarding Synthetic keys, it is recommended to review the data Structure in the table viewer, and also it is required to verify whether the data model is correct or not. Multiple Synthetic keys represent a symptom of an incorrect data model, but not necessarily. But often, Synthetic keys occur due to an error in the script.

If you would like to become a Qlik Sense Certified professional, then visit Mindmajix - A Global online training platform: " QlikView Online Training". This course will help you to achieve excellence in this domain.

Why should we remove Synthetic keys?

Synthetic keys don’t do any harm to the data model; it often provides easy to handle composites keys and help to verify the data when working on a good data model. But, when there are more synthetic keys, QlikView may take more time and money depending on the amount of data, structure, and other factors. It is not possible to predict the actual limitations that leave with the practical method to determine them using trial and error only.

More Synthetic keys and poor data model results in poor performance. To improve the performance, verify the data model and remove the Synthetic keys that help to provide the best performance. Synthetic keys may lead to poor data modeling. If an unexpected synthetic key is created, it is required to check the data model and make necessary changes that result in a good data model. It is required to create composite keys instead of synthetic keys that help to get good clarity about the data model.

Related Article: What is Qlikview

How to create a Synthetic key in QlikView

To create a Synthetic key in QlikView, do the following steps:

• Input Data

First, consider input data files that are used as input for further illustration.

• Load Script

Load the two tables into script or memory. Let’s assume that the data regarding the product details and sales details of two files. We used the two common fields for both the files, i.e., Product ID and Subcategory.

As soon as the files are loaded and saved in memory, use Ctrl+T to view the data model with a Synthetic key.

• Data model

The data model shows the creation of the third table that provides the value of the Synthetic key as the two tables have common fields in the data, i.e., Product ID and Subcategory as shown in the figure.

 

How to resolve the Synthetic keys

To remove synthetic keys, we should verify the data model and make necessary changes if required. Depending on the requirement, we have different ways to remove Synthetic keys. Let’s discuss them further:

• Renaming fields:

Just by renaming, one of the common fields can avoid the Synthetic key. For example, as in the figure, the two files have the same common name called City so we rename one of the fields as City As Dim_City which helps to avoid the creation of Synthetic keys. 

• Removing fields:

The fields that are common in the tables can create a Synthetic key in QlikView. We can remove such fields by making a comment before the field name like by adding ‘//’ to the field name from the script. For e.g. as in the figure, the two files have the same common field called City, one of the fields we remove is the Synthetic by adding a comment // to the City field name like //City.

• Renaming fields using Qualify:

You can also rename a field using the keyword Qualify. This way, we can rename the two common fields to avoid synthetic keys. With the help of the Qualify statement, field names are converted into TableName. FieldName format. For e.g., as in the figure, we renamed the common field called City with the help of the Qualify statement. And the field name is converted into Dim. City as shown in the figure.

• Autonumber:
  

  

By using Autonumberhash256 or Autonumberhash128 functions, we can resolve the synthetic keys. These functions create composite keys and generate a 256bit or 128bit value of each key field combination.

Related Article: Circular Reference in QlikView

Need for Removing Synthetic Keys

Synthetic Keys are created because of inadequate data modeling. Once an UNEXPECTED synthetic key is created, we will make some required changes to the existing data model, and as a consequence, we finish with a good data model. But if we have our own Composite Keys in place of Synthetic Keys, then we can build the Best Data Model.

Visit here to learn QlikView Certification Training in Bangalore

How to Remove or Fix Synthetic Keys

For removing synthetic keys and optimizing data models, we have another two essential techniques; they are as follows:

1. Link/Key Table

2. Concatenation

Now we will see these two techniques through the following example.

Let us take an XYZ Bank that has year-by-year transaction datasets, with one or two distinct fields, but the remaining fields are identical. Bank will display the Year by Year transaction datasets.

In this case, we will load Year by Year Transaction datasets. QlikView will create synthetic keys to combine these tables.

Data Loading through Link Tables

Example:

2018:

LOAD Customer_ID,

 Customer_Name1,

 Branch,

 Balance

FROM 

Linked_Table_Date1.xlsx

(ooxml, embedded labels table is Transactions);

2019:

LOAD Customer_ID,

 Customer_Name2,

 Branch,

 Balance

FROM

Linked_Table_Date2.xlsx

(ooxml, embedded labels table is Transactions1);

In the above script, we have loaded the data without using concatenate. As we have common fields in the tables, when we load the data, QlikView will create Synthetic Keys to combine tables.

Example for Concatenate

Transactions2:

LOAD Customer_ID,

 Customer_Name3,

 Branch,

 Balance

FROM

Linked_Table_Date3.xlsx

(ooxml, embedded labels table is Transactions2);

Concatenate

LOAD Customer_ID,

 Customer_Name3,

 Branch,

 Balance

FROM

Linked_Table_Date4.xlsx

(OOXML, embedded labels table is Transaction3);

In the above script, we have loaded data using concatenate. so we will combine the two tables as they have common fields like Customer-ID, Branch, Balance. As tables are combined, synthetic keys are not created by the QlikView.

Related Article: QlikView Questions and Answers

Removing Synthetic keys through Link Table

If we load this data into the QlikView, we will get a data model that includes Synthetic keys. For removing the synthetic keys, we will use the Link Table. Link Table will have all the common fields of the tables, and it will have all combinations of values. In the below screenshot you can see the tables with synthetic Keys:

Now we will remove the synthetic keys by creating a Link Table. The below screenshot displays the Link Table:

In the above screenshot, you can see the Link Table that includes the common fields like Product_Code, Branch, Employee_ID, key.

Explore QlikView Sample Resumes! Download & Edit, Get Noticed by Top Employers!

Conclusion

On wrapping up, usually, people want to remove Synthetic keys as it might impact the application in terms of speed and memory. Also, they don't do any harm to the data, but it may provide low performance as if there are more Synthetic keys. However, Creating synthetic keys helps to improve the data model. In this article, we have learned what the synthetic key is? How are they created? And how are they resolved? I hope you find the required information.