Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Tom1
Contributor
Contributor

Synthetic keys with quarterly data

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 viewInternal viewSource viewSource view

 

Labels (2)
3 Replies
Claudiu_Anghelescu
Specialist
Specialist

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



To help community find solutions, please don't forget to mark as correct.
Tom1
Contributor
Contributor
Author

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
Support (Former)
Support (Former)

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

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.