Synthetic Key in QlikView

What is Synthetic Key in QlikView

The Synthetic Key is available only when two or more fields are associated between tables; a Synthetic Key and table is then formed. The Synthetic Key is not good for a designed data model. Always remember that two tables can be connected by one field. That shows up as a connection. Two tables can be connected by two or more fields. That shows up as a Synthetic Key. You should not connect two particular tables by one field. When one or more tables have two or more fields in common, then  a composite key will be generated, then QlikView handles this process with a Synthetic Key. These keys are anonymous fields that represent combinations of the composite key. When the number of  composite keys increase, then it depends upon the total amount of data and the table structure. QlikView creates additional tables with Synthetic Keys ($Syn).

The Synthetic Key is always represented with $Syn.

How to avoid a Synthetic Key from tables

By using these points, we can avoid Synthetic Keys from tables.

• Renaming the table fields
• Commenting the fields.
• Joining of tables
• Concatenation of tables
• By using complex keys
• Link table

Example

Suppose you have two tables and there are two or more fields in common, then the following is how the Synthetic Key is executed:

TableA: FieldA, FieldB, FieldC
TableB: FieldA, FieldB, FieldD

After reloading these table, you will actually get this:

TableA: SyntheticKey, FieldC
TableB: SyntheticKey, FieldD
SyntheticKeyTable: SyntheticKey, FieldA, FieldB

The following is the procedure to create Synthetic Keys in tables.

Step 1: Open the QlikView application

In the first step you need to open the QlikView application then go to File -> New then this window will be opened.



Step 2: Open Edit Script

The second step is to open the edit script window from File -> Edit Script.



Then this window will be opened.



Step 3: Click on table files

The next step is to click on the table files you need to open, data files like Excel or CSV. These files contain the data.



Now open a table file and click on the Open button.



Step 4: File wizard type

Then this window will be opened. Here you can see your table file table and click on the Finish button.



Step 5: Code of edit script

Now, in the edit script the Excel file was uploaded successfully.



By following this process, we can upload another Excel file.

Now you will see these two Excel files in the edit script. Here you can see, in two different tables two fields are in common, like City and EmpId.



Step 6: Save file

The next step is to save our QlikView file.



Step 7: Reload the edit script.

The next step is reload the code of the edit script.



Step 8: Sheet property window

The sheet property window will be opened and click on the OK button. Here you will see two fields are shown as a Synthetic Key, like City and EmpId.



Step 9: Select table viewer

The next step is select table viewer from the menu tab.



Step 10: Now you will see Synthetic Key. Here you will see the Synthetic Key shows the common fields.

Summary

This article described what a Synthetic Key is in QlikView and how it shows the common fields of a table.