Synthetic key problem

[#1]

Hello,

i'm trying to link some tables and im getting a synthetic key.i have for example 4 tables:

table1,table2,table3 are related with a key1

AND

table2,table3,table4 are related with another key2

key1 and key2 are going to create a synthetic key between table2 and table3.

Note that there are no other keys i can use to link these tables.

is there any other way to link these tables without creating a synthetic key?does the synthetic key have any impact on the performance?can i leave it or not?

please find attached an example of what i'm trying to ask about:)

hope you can help me with this,

thank you...

[nagaiank]

Create a new field 'CombinedKey' using the function AutoNumber(SKUKEY & '|' & StoreKey)

Tour table keys should be as follows:

(1) ITEMS table Fields

       SKUKEY (key)

(2) Store table

      Store key (Key)

(3) Sales Detail table fields

     CombinedKey (key)

     SKUKEY

     Store key

(4) Balances table

     CombinedKey (key)

     SKUKEY

     Store key

This will prevent the synthetic keys.

[#3]

I tried it!

this will only prevent the synthectic keys but this way

ITEMS table and won't be connected to Balances table and Sales Detail table

and store table won't be connected to Balances table and Sales Detail table too.

I need all these 4 ables connected using these 2 keys.

[johnw]

If it is appropriate for the sales details table and the balance store table to be linked together by both SKUKEY and StoreKey, then the synthetic key that QlikView created is an appropriate way to link them and should be left alone.

Full disclosure - this is my personal opinion, and my personal opinion is still controversial.  We do not have expert consensus one way or another.  My views are presented in this thread:

http://community.qlik.com/thread/10279?start=30&tstart=0

As far as krishnamoorthy's suggestion, that's pretty much the typical approach taken (and even suggested by the reference manual), except for minor errors in presentation.  The combined key should be built as described, but you need five tables instead of four:

• Key Table: CombinedKey, SKUKEY, StoreKey
• Sales Details Table: CombinedKey, other fields
• Balance Store Table: CombinedKey, other fields
• ITEMS TABLE: SKUKEY, other fields
• Store Table: StoreKey, other fields

All the tables are now connected properly, some just a little indirectly through your new Key Table.  However, this layout is equivalent to what the synthetic key provides, and I believe there is no performance benefit to switching to this new data model.  I believe all it would do is make your load script more complicated and slower to execute.

[#5]

is using a link table can help me??

[johnw]

The "Key Table" I mentioned is a link table.  It can help you remove the synthetic key.  But I don't think it will improve load or chart performance in any way.  Did I misunderstand the question?

[#7]

you did understand my question,you're right John it only helps in removing the synthetic key.i'm still trying to fix it anyway.

for no what i did is creating another store table similar to the original one but of course with different field names and i linked one to the sales and the other to the Balance Store table!

i couldn't find any other way to get the right results!