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.
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.
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
(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
//Drop Table [Projektkategori]