QlikView Synthetic Keys

In QlikView, a synthetic key is a key that is created when two or more fields in different tables have the same name and data type, but are not linked by a common key field. Synthetic keys can occur when two or more tables are joined in a QlikView script, and can cause issues when creating charts and tables because QlikView may not know which field to use as the key.

To avoid synthetic keys, you should ensure that all tables in your QlikView application have a unique key field that can be used to link the tables together. If you do not have a common key field between two tables, you can create a synthetic key using the Concatenate function to combine the fields that you want to use as the key.

For example, suppose you have two tables with the following structure:

Table1: ID | Name | Age ------------------- 1 | John | 35 2 | Mary | 28 3 | Tom | 42
Table2: ID | Name | Salary ----------------------- 1 | John | 50000 2 | Mary | 60000 3 | Tom | 55000

To link these two tables together, you can use the ID field as the key field. However, if the ID field is not present in one of the tables, you can create a synthetic key using the Concatenate function to combine the Name and Age fields from Table1 and the Name and Salary fields from Table2.

An example of how to create a synthetic key using the Concatenate function:

Data:

LOAD *, Concatenate([Name], [Age]) as SyntheticKey FROM [lib://Table1.qvd] (qvd); JOIN LOAD *, Concatenate([Name], [Salary]) as SyntheticKey FROM [lib://Table2.qvd] (qvd);

This will create a synthetic key field called SyntheticKey that combines the Name and Age fields from Table1 and the Name and Salary fields from Table2. You can then use the SyntheticKey field as the key field to link the two tables together.

It is generally best to avoid using synthetic keys if possible, as they can cause confusion and make it more difficult to work with your data. Instead, try to use a common key field to link your tables together whenever possible.

A synthetic key in QlikView is a key that is created when two or more tables are joined together, but none of the fields used in the join contain unique values. This can lead to duplicate rows in the resulting table, and can cause issues when creating charts and visualizations. To fix a synthetic key, you can either create a new field with unique values to use as the join key, or you can use the “Concatenate” function to combine multiple fields together to create a new, unique key.