Removing Synthetic key

[kishorj1982]

Hi All,

PFA.

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

Thanks

[#2]

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.

[Gysbert_Wassenaar]

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

See this blog post: Synthetic Keys

[Oleg_Troyansky]

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