QlikView Tips & Tricks: The Link Table

In this blog, I will show you how to bypass a “Synthetic Key” table in QlickView.

Why bypassing a “Synthetic Key” table?

If you have multiples links between two tables, QlikView generates automatically a “Synthetic Key” table. (here “$Syn 1” table).
The QlikView best practice recommend to remove such kind of key table for a question of performance and “correctness” of the result.

How to bypass this “Synthetic key” table?

The “Link Table” is the solution to bypass the generation of a synthetic key table.

This table will contain two kind of fields:

• A “foreign key”, made with the fields that are common to the two tables
• The fields that have been used to create the new “foreign key”

This “Link Table” will have the following structure:

In our case, the structure of the “Link Table” will be the following:

How to proceed?

Add the needed fields in the linked tables

Before creating the “Link Table”, we must add the fields in the tables that we should linked together.
Remark: A best practice to create this “Foreign_Key” field is to separate the different fields with “|”.
So, in our case, the fields in the table SALESDETAILS will be added as follow:

The fields in table BUDGET will be added as follow:

Create the “Link table”

The fields to create the “Link Table” are now added. So we can create the table as follow:
Click on “Tab / Add Tab” and name it “LINK_TABLE” (1).

Type the following script:

(1) The name of the table
(2)The names of the fields should be the same in each table
(3) Use the CONCATENATE instruction

Reload the data (1) and check the result (2)

The result should be like this: