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:
num(month(Date)) as Maand#,
year(Date) as Year
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?
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)
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.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.