Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have multiple tables with 1 or 2 fields in common that
i need to all link, however i get a synthetic key or loop everytime i reload. I
tried to make a key to fix this, but since some of my tables are cross tables i
dont think i can comment out the main, named field to do that - in this case
"Month_Ref". Any suggestions?
Ex.
Table1:
Crosstable (Month_Ref, Forecast, 2)
Material,
Plant,
Month 1,
Month 2,
Month 3
Table 2:
Crosstable (Month_Ref, Shipments, 2)
Material,
Store#,
Month1,
Month2,
Month3
CalendarTable:
Month_Ref,
Month_Text,
Month_Date
MasterTable:
Material
Another note to add would be the the CalendarTable: Month_Ref also has
fields that would link to Month_Ref in the other tables to then show date as
text depending on the particular month number (Month1, Month2, Month3), or
month date in actual date format, etc. So, with this being said, i do not
believe that i should rename the Month1, Month2, and Month3 fields as it may
jeopardize my Calendar.
I appreciate the help!
Parrish
There is a widespread misconception that synthetic keys always are bad. They're not.
As I see it, you have two options:
1: Use the crosstable Loads as temporatry tables and create your own compsite key in a second step.
2. Leave the model as it is.
My view is that you should leave it as it is - that is, if you don't have any other problems with it. It looks OK to me.
HIC
See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys
Ok, I will try option 2, but if I add more tables, is it ok to have multiple synthetic keys? Also, what if I get loops as well as synthetic keys? I am not overly familiar with why loops occur or how to fix them without fixing the synthetic keys as well.
Thanks,
Parrish
Synthetic keys made from other synthetic keys are not OK. Then I would load the crosstables in two steps instead, e.g.
tmpTable1:
Crosstable (Month_Ref, Forecast, 2)
Material, Plant, Month 1, Month 2, Month 3 ...
Table1:
Load Forecast,
Plant,
Autonumber(Month_Ref & '-' & Material) as %MonthXMaterial
Resident tmpTable1:
tmpTable2:
Crosstable (Month_Ref, Shipments, 2)
Material, Store#, Month 1, Month 2, Month 3 ...
Table2:
Load Shipments,
Store#,
Autonumber(Month_Ref & '-' & Material) as %MonthXMaterial
Resident tmpTable2:
Table3:
Load distinct Month_Ref, Material,
Autonumber(Month_Ref & '-' & Material) as %MonthXMaterial
Resident tmpTable1;
Load distinct Month_Ref, Material,
Autonumber(Month_Ref & '-' & Material) as %MonthXMaterial
Resident tmpTable2;
Drop Table tmpTable1, tmpTable2;
HIC