How to avoid synthetic key

[zahidrahim_ocp]

Dear Experts,

I have a master table with composite PK. Item_id and Organization_id. And a detail table has few records match with master table. When i load data keeping both column name same to join them it creates a synthetic key. I dont want to see it in my data model as the direct join is correct.

How to avoid it somehow?

Regards,

Zahid Rahim

[ahaahaaha]

Hi,

May be manually will create a synthetic key as a single field?

Regards,

Andrey

[ahaahaaha]

Example createing syntetic key manually in attached file

[zahidrahim_ocp]

Thankyou for the response but it will create a separate table which will create my data model complex as i have 25 to 30 tables in the data model. Is there any other workaround i heared about Autonumber()

[ahaahaaha]

You can use Autonumber() instead of applying field names, the essence of this does not change. With large amounts of data, perhaps Autonumber() will work somewhat faster. If the amount of data is relatively small (hundreds of thousands - several million lines), there should not be much difference.

[marcus_malinow]

Hi Zahid,

given that your master table contains the Item ID, and Organisation ID, and both master and detail tables have the composite, I would drop Item ID and Organisation ID from your detail table.

Marcus

[muthukumar77]

Hi,

Refer this,

https://www.analyticsvidhya.com/blog/2014/11/synthetic-keys-qlikview-simplified/

[zahidrahim_ocp]

If you dropped from detail table then how the tables get joined?

[marcus_malinow]

zahid,

you'll need to create your composite key in your detail table.

Marcus

[Miguel_Angel_Baeyens]

The synthetic key is how QlikView works by default when two tables share two or more fields named alike.

In itself, it is not wrong if you want it so, in this case, you have both fields Organization_ID and Item_ID in at least two tables, and this may be correct. It will display as an additional table in QlikView data model because that is how it is actually stored: you need the pointers for those two columns and its unique values.

Synthetic keys can be a warning if you are not expecting them to happen and can affect both performance and the results in your charts if they are not generated on purpose.

As suggested, you could create yourself a composite key using both fields in the fact and dimensional tables.