What is Synthetic Key?

Synthetic Key Basics

When two or more tables shares more than one common field, QlikView join tables with all fields in common, and this creates an additional table with fields which share both tables.

An example of a data model with synthetic keys.

Qlikview automatically associates common fields, created a synthetic key or complex key and leading to a third table called $Syn 1 Table. 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. 

How to eliminate synthetic keys?

There are number of ways, we can avoid QlikView to form Synthetic Key.

1. Using QUALIFY

This enforces QlikView to qualify all/selected fields. Qualify implements the check on full path of the field (e.g. Field is created as combination of Tablename.FieldName).

For Example.

Qualify *;
Turns qualification on for all field names of the subsequent table(s).

Qualify “*_Name”;
Turns qualification on for all field names ending with _Name in the subsequent table(s).

Qualify “Name_*”;
Turns qualification on for all field names Starting with Name_ in the subsequent table(s).

Qualify “Amt*”,Profit;
Turns qualification on for Profit and all field names starting with Amt.

Qualify S???;
Turns qualification on for four character field names starting with S.

However, always there is no need to qualify ALL the fields of a table. e.g. you have C1 & C2, two common columns between Table 1 & Table 2. C1 is used to join the 2 tables and C2 is the column that you think might create Syn Key. You will do something like:

QUALIFY *;

UNQUALIFY C1;

Select * from Table1;

Select * from Table2;

This will ensure that only C1 is used to join the two tables, eventually eliminates the possibility of creation of Synthetic Key.

2. Autonumber

If you want to concatenate the Synthetic Key candidate fields, and create your own key, you can use following Autonumber functions.

• Autonumber
• Autonumberhash128
• Autonumberhash256

This will create a unique integer value for each distinct combination of the concatenated columns. Autonumberhash128 and Autonumberhash256 creates 128 bit and 256 bit values respectively.

e.g. In the previous example, Autonumber(C1 & C2) or Autonumberhash128(C1 & C2) or Autonumberhash256 (C1 & C2) will create the necessary uniqueness.

As Autonumber is generated by system, you do not have any control over the values. Also for external QVDs, where the range of unique Autonumber values are limited, uniqueness is not guaranteed. The "hash128" and "hash256" functions specifically address this issue and widens the numerical range to ensure uniqueness.

3. Alias

We can rename the conflicting fields (which are part of the synthetic key between the two tables) using alias. This ensures the field uniqueness.

4. Concatenate and form new Key

We can create a new key with the concatenation of all common columns. After creating the new key, we can remove the conflicting fields from either table.