Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
Showing results for 
Search instead for 
Did you mean: 
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

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.
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.

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


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.
Partner - Master III
Partner - Master III

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


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".


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;

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]