Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "Luminary Alumni"){kind=icon}
dvqlikview wrote: Synthetic keys are generally resource heavy and may slow down calculations and, in extreme cases, overload the application. They also make a document harder to understand and maintain.
In your case, can you create a composite key on the columns?
Synthetic keys are in and of themselves not resource heavy, will not slow down calculations, and will not overload the application. They are QlikView's version of composite keys, no more, no less. Creating direct replacement composite keys will only waste script time. The reason they are typically associated with horrible performance, script crashes and the like is that they typically result from data model mistakes, and those data model mistakes are what are really causing the problems. And often, data model changes made to remove the synthetic keys also fix the data model mistakes, reinforcing the idea that the synthetic keys are to blame.
Let's look at this specific example. I'll start with with what Cheenu says, which sounds right to me:
CheenuJanakiram wrote:It looks like you need a massive link table which will contain all the individual values from the 4 source tables in your image for the fields DossierCode, HourCode, ProductHeaderDossierCode. This link table will then need a concated field with HourCode & ProdHeaderDossierCode to link to the ProcessedHour table, a concat of DossierCode & ProdHeaderDossierCode to link to the ProductionHeader and a concat of the DossierCode&ProdHeaderDossierCode&HourCode to link to the HourBudgetTranslate.
Agreed. But here's the thing - that is exactly what the synthetic key structure is. QlikView itself came to this exact same conclusion about the data structure, and it did it all for you.
A synthetic key table is a concatenated key table. The only real difference is that QlikView builds it for you automatically, and you cannot reference the table or the new field, but there is a table and a field internally. And if you look at the structure of these tables and fields, you'll see that it exactly matches the description of the required link table and the keys necessary to connect all the tables to this link table.
In other words, if we have correctly understood what sort of linkage table you need, then you already have that kind of linkage table. To try to create it manually when QlikView has already built it for you automatically would be a waste of time, even if it might be an interesting learning experience, and perhaps worthwhile for that reason alone (though you'd want to go back to synthetic keys if script performance became an issue).
So, two possibilities here. If I've understood what sort of linkage table you need, you're done. Your data model is already correct, and should be producing correct results. Changing to a manually-created linkage table will only slow down the load, not speed up the application. But if you are not seeing correct results in charts, then I've misunderstood your data model, and I'd need to look more closely. But if you are not seeing correct results, then switching to a linkage table that exactly mimics the synthetic key structure will not fix the problem. You would need to make some other sort of data model changes.
More information on synthetic keys in this thread:
http://community.qlik.com/forums/t/31028.aspx
I should probably turn it into a wiki entry at some point, but I think the discussion helps present a more balanced viewpoint than I'd be able to present on my own, given my perhaps obviously strong opinions on this subject. 
Hello John, I removed the manual link table stuff, I let QV create the synthetic keys automatically and since I am not seeing the correct results I assume I have to do something else. Let me try to explain what I would like to accomplish.
I have a table "ProcessedHour" that contains all workinghour records of our employees, the hours are booked against a specific hourcode (holiday, doctor ...10 categories in total.) or they are booked against a combination of HourCode and ProdHeaderDossierCode (Project or Product specific). Next to that table I also have a "HourBudgetTranslate" table that contains 21 categories. Out of those 21 categories 9 are the same as the ones from the ProcessedHour table but one is translated to different categories (Sales, Meeting, education etc.) based on the ProdHeaderDossierCode or DossierCode where DossierCode is a combination of ProdHeaderDossierCodes.
I would like to be able to select one of the 21 categories from the HourBudgetCode table and the report should show me all processedhour records that belong to that specific category. The HourBudgetTranslate table contains manually entered links between HourCode - HourBudgetCode; ProdHeaderDossierCode - HourBudgetCode and DossierCode - HourBudgetCode. So if I enter a specific HourCode, ProdHeaderDossierCode or DossierCode in the HourBudgetTranslate table linked to one of the 21 categories the hours recorded in the ProcessedHour table against that DossierCode or ProdHeaderDossierCode should be included in the report. This is what seems to be missing right now.
Hope this explains what I am trying to do. I have added to the 4 tables I started with (see picture below) but have not found a solution to the scenario above. I appreciate any help/tips you can give me.
Kind regards, Diego
John, sorry for the confusion. You are correct, HourBudgetCode is the category in the HourBudgetTranslate table and HourCode is the other category in the ProcessedHour table. Attached is another file with a bit more data.
I have added a second tab with the HourBudgetCode including description and HourCode including description. What you will see is that HourCode 10 relates to several HourBudgetCodes and the rest of the HourCode relate to just one HourBudgetCode. I believe it is HourCode 10 where I need to script something so it will show me the associated ProcessedHour records. So if the ProcessedHour record contains HourCode 10 I need to check the HourBudgetTranslate table if the ProdHeaderDossierCode from the ProcessedHour record is in the HourBudgetTranslate table, if so it should link to the associated HourBudgetCode, if it's not then I need to check the HourBudgetTranslate table again for the DossierCode. To do this I first need to retrieve the DossierCode that belongs to the ProdHeaderDossierCode.
I noticed I forgot to add DossierCode to the ProductionHeader table I believe I need this to retrieve the DossierCode that belongs to the ProdHeaderDossierCode. I added the latest tableoverview below.
Hope this clarifies a bit more.
