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.