topic Re: avoiding Synthetic Keys in QlikView

avoiding Synthetic Keys

patrickbender — Tue, 20 Nov 2018 19:32:22 GMT

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.

Re: avoiding Synthetic Keys

Vegar — Tue, 20 Nov 2018 19:42:26 GMT

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.

Re: avoiding Synthetic Keys

dplr-rn — Tue, 20 Nov 2018 21:21:19 GMT

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-Synthetic-Key/m-p/213649
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.

Re: avoiding Synthetic Keys

Vegar — Tue, 20 Nov 2018 21:55:48 GMT

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.

Re: avoiding Synthetic Keys

dplr-rn — Tue, 20 Nov 2018 22:01:34 GMT

Agreed. Hence the caveat at the start 🙂

Re: avoiding Synthetic Keys

patrickbender — Tue, 27 Nov 2018 13:50:37 GMT

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]