Synthetic Keys

Donnie Bryson

Simple Linkage

Qlik assumes that the fields with the same name between tables should be linked. For example, the following two tables would load and link the two tables together.

/* donnie1 and donnie2 link together via ID */
donnie1:
LOAD * Inline [
ID , brand, criteria, assesement
123 , abc, Q9, Y
145 , abc, Q9, Y
123 , abc, P11, Y
145 , abc, P11, N
];

donnie2:
LOAD * Inline [
ID , brand2, criteria2, assesement2
123 , dc2 , T9, good
145 , dc2 , T9, better
123 , dc2 , T11, best
145 , dc2 , T11, bad
];

Looking at this in the Data Model Viewer, it displays as such:

Creating a Synthetic Key Correctly

Some data links together by multiple fields. For example, a salesman might sell an item one day and sell another one the next day. So, the index would be the employee number plus the date. Different database technologies refer to this differently. Qlik refers to it as a synthetic key. Basically, the system creates a new table that contains the multiple fields that points to the different tables.

Here is an example of a load script that creates a synthetic key:

/* this is the correct way to use synthetic keys */
donnie5:
LOAD * Inline [
ID3, saledate, amount
123, 1/1/2020, 23.30
145, 1/1/2020, 50.55
123, 1/2/2020, 33.30
145, 1/2/2020, 60.55
];

donnie6:
LOAD * Inline [
ID3 , saledate, dept
123 , 1/1/2020, dc2 
145 , 1/1/2020,ndc2
123 , 1/2/2020, dc2 
145 , 1/2/2020,ndc2
];

This is what these tables look like in the Data Model Viewer:

This is what this data looks like put into a simple table:

Accidental Synthetic Key

Naming keys the with the same name can unintentionally create synthetic keys that setup bogus relationships between tables. Let’s say we have named field criteria3 in two tables that are NOT the same thing. One is validated against the location and the other is validated against quantity size. Naming them to same will result in none of the two tables to match.

/* this is a mistake by naming criteria3 with the same fieldname */
donnie3:
LOAD * Inline [
ID2 , brand3, criteria3, assesement3
123 , abc, Q9, Y
145 , abc, Q9, Y
123 , abc, P11, Y
145 , abc, P11, N
];

donnie4:
LOAD * Inline [
ID2 , brand4, criteria3, assesement4
123 , dc2 , T9, good
145 , dc2 , T9, better
123 , dc2 , T11, best
145 , dc2 , T11, bad
];

The above load code generates a bogus relationship based on ID2+criteria3. The important lesson is verify that naming fields with the same name is what is intended.

Tag: load