Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
patrickbender
Contributor III
Contributor III

avoiding Synthetic Keys

Hi I have a few tabels that I want to compare and make calculations on based on dates.

The problem is that these dates are not comparable. 

I have tried to do a linkTable with a master Calendar which works if multiple dates are in the same table. But when they are in different tables it creates loops which corrupts the data more that a synthetic key.

On the right I also have a "correct" synthetic key in order to use interval match.

What alternatives are there if  I want to be able to compare the data of different tables on the same timeaxis? I attach the table view of my data.Synthetic.JPG

 

 

5 Replies
Vegar
MVP
MVP

Create a concatenated field of the two.

Hash256(Projektnr,DatumYM) as %projektdatum

Replace Projektnr and DatumYM with this new field in all but one table.
dplr-rn
Partner - Master III
Partner - Master III

Caveat to below points - i try to avoid synthetic keys whenever possible

Synthetics keys are a big discussion point in qlik community.
Many including the esteemed henric argue its not a big deal as far as performance is concerned. and that the bigger question is the datamodel.
In many cases synthetic key is a symptom of data model mistake.

https://community.qlik.com/t5/QlikView-App-Development/Should-We-Stop-Worrying-and-Love-the-Syntheti...
https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

I would say read the posts and check the data model in detail.

Vegar
MVP
MVP

Its true what @dplr-rn writes. Synthetic keys aren't necessary bad, but I will argue that it is a good rule of thumbs to eliminate all synthetic keys.

When you have become an QlikView Qlik Sense expert you will find a few scenarios where synthetic keys could be the preferred solution, but for now I would recommend you to clean up all of them.
dplr-rn
Partner - Master III
Partner - Master III

Agreed. Hence the caveat at the start 🙂
patrickbender
Contributor III
Contributor III
Author

Hi,

I'm trying to remove the synthetic keys while still using my lookup table.

 

 

This is my code:

 I use a Left Join to avoid getting new records as Henric writes about (and remove the synthetic key).

But I also want to use Left Join on "ProjektBenämning" and "ProjektKategori " in order to get one table with all the data I need. However putting Left join between the tables result in an error saying that QV can't find the table "Projektkategori".

 

ProjektBenämning:
LOAD [FCHACC_Konteringsbegrepp] as Projektnr,
[FCHACC_SpärratKonteringsbegrepp] as ProjektSpärr,
[FCHACC_Beskrivning] as ProjektNamn,
[FCHACC_Konteringsbegrepp] & ' ' & [FCHACC_Beskrivning] as ProjektNrNamn
FROM [N:\CSVfiler\Konteringsbegrepp_BYG.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
WHERE [FCHACC_Konteringsdimension]= 4;

[Projektkategori]:
LOAD [ProjektHuvudGrupp] As ProjektHuvudGrupp,
[Projektgrupp] As Projektgrupp,
[Från] As fromProject,
[Till] As toProject,
[Ordning] As Projektordning
FROM [N:\Byggsystem\QVS uppdelning.xlsx]
(ooxml, embedded labels, table is Projektkategori);

IntervalMatch ( Projektnr )
Left Join ([Projektkategori])
LOAD fromProject, toProject
Resident [Projektkategori];


//Drop Table [Projektkategori]