topic Re: Synthetic keys with quarterly data in QlikView

Synthetic keys with quarterly data

Tom1 — Sat, 16 Nov 2024 04:07:52 GMT

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

Re: Synthetic keys with quarterly data

Claudiu_Anghelescu — Sun, 24 Mar 2019 16:12:52 GMT

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];

Re: Synthetic keys with quarterly data

Tom1 — Tue, 26 Mar 2019 13:42:46 GMT

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)

Re: Synthetic keys with quarterly data

Brett_Bleess — Wed, 10 Apr 2019 21:13:05 GMT

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