topic Synthetic key problem in QlikView

Synthetic key problem

Wed, 29 Jun 2011 15:54:57 GMT

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...

Synthetic key problem

nagaiank — Wed, 29 Jun 2011 16:05:09 GMT

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.

Synthetic key problem

Wed, 29 Jun 2011 16:12:31 GMT

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.

Re: Synthetic key problem

johnw — Wed, 29 Jun 2011 23:24:23 GMT

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.

Synthetic key problem

Thu, 30 Jun 2011 08:30:56 GMT

is using a link table can help me??

Synthetic key problem

johnw — Thu, 30 Jun 2011 16:18:02 GMT

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?

Synthetic key problem

Thu, 30 Jun 2011 22:31:05 GMT

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!