Infrastructure at your Service

Matthieu Munch

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.

1_QV_Link_Table.PNG

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:

2_QV_Link_Table.PNG

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

3_QV_Link_Table.PNG

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:

4_QV_Link_Table.PNG

The fields in table BUDGET will be added as follow:

5_QV_Link_Table.PNG

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).

6_QV_Link_Table.PNG

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

7_QV_Link_Table.PNG

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

8_QV_Link_Table.PNG

The result should be like this:

9_QV_Link_Table.PNG

 

Leave a Reply

Matthieu Munch
Matthieu Munch

Consultant