topic Re: Key and Crosstable Help in QlikView

Key and Crosstable Help

Wed, 24 Jul 2013 18:55:53 GMT

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

Re: Key and Crosstable Help

hic — Wed, 24 Jul 2013 20:16:13 GMT

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

Re: Key and Crosstable Help

Wed, 24 Jul 2013 20:24:18 GMT

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

Re: Key and Crosstable Help

hic — Thu, 25 Jul 2013 17:27:45 GMT

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