topic Re: Removing Synthetic key in QlikView

Removing Synthetic key

kishorj1982 — Wed, 03 Feb 2016 17:46:59 GMT

Hi All,

PFA.

Can you please suggest how could I remove synthetic key In attached screenshot.

Thanks

Re: Removing Synthetic key

Wed, 03 Feb 2016 18:37:10 GMT

Is Country & Year is a Key between all tables ? Either you can create a Link Table or composite key to remove the synthetic keys.

If you have same Country & Year values across all tables, you can create Composite key to remove the synthetic keys.

Place new field AutoNumber(Country &'|'& Year) AS %CountryYearKey in all table and keep Country & Year in any one of table. So all tables will be join on %CountryYearKey.

Re: Removing Synthetic key

Gysbert_Wassenaar — Wed, 03 Feb 2016 18:42:52 GMT

I suggest you leave it in place unless you're certain that it is causing performance problems.

See this blog post: Synthetic Keys

Re: Removing Synthetic key

Oleg_Troyansky — Wed, 03 Feb 2016 18:47:27 GMT

People that know me, won't believe that I'm saying it out loud, but this kind of a Synthetic Key is just fine, it doesn't necessarily need to be eliminated. If you are going to build a Composite Key and a Link Table, they will work just the same way...

On the other hand, it looks like you have multiple Fact tables, all sharing two key fields. My recommendation would be to build a Concatenated Fact table that combines all these facts in one, - especially if you are expecting a lot of data in these tables. This would not only eliminate the need in the Synthetic Key, but will also substantially improve performance.

Check out my book QlikView Your Business, where I describe in detail how to eliminate Synthetic Keys, how to build Link Tables and Concatenated Facts.

cheers,

Oleg Troyansky