Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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]