Key and Crosstable Help

[#1]

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

[hic]

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

[#3]

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

[hic]

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