Why should synthetic keys be removed from QlikView applications?

[Sonja_Bauernfeind]

This example has the following conditions:

• Show the Total Sales during a period of time
• Show the Average Sales during a period of time

A straight table showing the results:

Sum of Sales is showing the correct value 2+16+5+4+4+2+7+11=51

Average Sales  is showing 5,1 i.e. 51/10=5,1 and that seems to be correct according to the table above, but the value isn't correct based on the loaded data.

From the source table Transaction there are only 8 Sales occurrences. Expected result for Average Sales is 51/8=6,375.

Resolution

The data model must be adjusted so that the two table only have one common field, which will resolve the synthetic key.

In this case only the field SalesPerson in table SalesPerson will be loaded.  

The synthetic key is resolved and a new key created:

Both Sum of Sales and Average Sales are now displaying the expected and correct values:

Synthetic keys aren't always the root cause when something goes wrong but it can be hard to foresee what the impact might be. Therefore, always make sure that synthetic keys are removed from the data model. 

Cause

The issue appears because of a synthetic key in the data model. The synthetic key is show as table named with $Syn as prefix.

Two extra rows appear:

This happens because of the synthetic key and is a result of values found in table SalesPerson: