Synthetic keys with quarterly data

[Tom1]

Hi all,

I'm quite new to QlikView, and have some questions regarding the use of synthetic keys.

Within my data model, I've got two data sources:

1) A monthly data dump containing all kinds of information about the employees of the company. This file contains one row per employee, and each employee has a unique employee ID. (Main)

2) A quarterly dump of all the overtime that has been worked. The relevant fields in this file are Employee ID, Date, and Hours. Each row is a seperate instance of overtime worked, and as such there could be multiple rows for one employee. (Overwerk)

In order to combine the data into monthly and quarterly reports, I use the following code snippet in the script of both data sources:

Load,
Employee_ID,
//Other things
num(month(Date)) as Maand#,
year(Date) as Year
From
[File];

Of course, since these three fields now exist in both tables and the overtime has multiple records for some employees, QlikView creates a synthetic key in order to combine the tables.

This seemed like a perfectly logical solution to me. However, after doing some reading online I discovered that syn keys are generally regarded as a 'bad thing'. But, some other folks say they aren't such a bad thing if required by your data model (with the footnote that it is rarely required and that you should think hard about whether it is actually required).

With the background out of the way, I have the following questions:

Would you replace the synthetic key with a different solution in this situation?

I've seen some folks argue that a simple concatenated key table should be used to replace a synthetic key. Why is that solution better than letting the synthetic key remain as-is?

Following the school of thought that syn keys can sometimes be useful: how do you recognise such a situation?

Internal viewSource view

[Claudiu_Anghelescu]

You have to rename some fileds in order to eliminate the Synthetic Keys.

Load,
Employee_ID,
//Other things
num(month(Date)) as Maand_,
year(Date) as Year_
From
[File];

[Tom1]

The thing that I want to be able to do is separate the amount of overtime worked by department (where the department field is only present in the Main table) for each month. For example: assume an employee that transferred from department A to B in July. The overtime overview for June should include their overtime in department A, and in department B for July.

@Claudiu_Anghelescuwouldn't your suggestion result in the above example not work anymore, because the month field for overtime is not the same month field for main?

Could you also expand a bit on why synthetic keys should be eliminated? (Because that's my main question at the moment)

[Brett_Bleess]

Take a look at the following link, Design Blog post from Henric Cronstrom regarding synthetic keys, hopefully this will give you all the details you need to sort things out.  It is generally better to control the links versus using the synthetic keys/tables, as many times those can result in expressions returning results you were not expecting etc.

https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

Regards,
Brett