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'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);