topic Re: How to avoid synthetic key in QlikView

How to avoid synthetic key

zahidrahim_ocp — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to avoid synthetic key

ahaahaaha — Thu, 17 Aug 2017 12:35:54 GMT

Hi,

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

Regards,

Andrey

Re: How to avoid synthetic key

ahaahaaha — Thu, 17 Aug 2017 12:39:49 GMT

Example createing syntetic key manually in attached file

Re: How to avoid synthetic key

zahidrahim_ocp — Thu, 17 Aug 2017 13:15:38 GMT

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()

Re: How to avoid synthetic key

ahaahaaha — Thu, 17 Aug 2017 13:19:50 GMT

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.

Re: How to avoid synthetic key

marcus_malinow — Thu, 17 Aug 2017 14:00:35 GMT

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

Re: How to avoid synthetic key

muthukumar77 — Thu, 17 Aug 2017 14:44:33 GMT

Hi,

Refer this,

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

Re: How to avoid synthetic key

zahidrahim_ocp — Mon, 21 Aug 2017 11:31:13 GMT

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

Re: How to avoid synthetic key

marcus_malinow — Mon, 21 Aug 2017 12:03:58 GMT

zahid,

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

Marcus

Re: How to avoid synthetic key

Miguel_Angel_Baeyens — Mon, 21 Aug 2017 12:28:12 GMT

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.