QlikView – Resolving Synthetic keys

Synthetic table and Synthetic keys gets created when there are more than one common fields between the 2 or more tables.

QlikView associates tables based on the common field names in the tables and if there is more than one common field then QlikView creates  synthetic tables.  Synthetic keys are not beneficial in datamodel because they are resource intensive and may slow down calculations.They also make datamodel hard to understand. Synthetic keys should always be resolved.

Steps:

  • I have loaded 3 tables Products,Orders and OrderDetails

Products_syn

orders_syn

Orderdetails_syn2

  • Load the table and analyze your data model in Table viewer and observe the joins
TableViewer_Syn

ProductID and UnitPrice and common in Products and OrderDetails table and therefore created synthetic keys.

  • Make sure that Table associates only on the required fields. since Products and OrderDetails table are linked on ProductID, we can remove Unitprice from the table. You can do so either by commenting Unitprice or renaming it. Let us rename Unitprice in Products table by using “AS”

Products_synAS

  • Now load the tables again and check the Table viewer. Observe that Synthetic table/Key is removed and Products and OrderDetails are linked on ProductID.

TableViewer_Syn2

Please watch my video to see in detail

9 thoughts on “QlikView – Resolving Synthetic keys

  1. To a new person that is new with tables and database this explanation could be a huge help for them, even for me! 🙂 Simple and precise…

  2. Lets say I’ve created two filters and the common keys which are date and name form a synthetic key. How should I resolve that particular situation if I still need both keys for the filters?

    • Luis:
      When you say you created 2 filters ..do you mean 2 list boxes.
      Synthetic key occurs between 2 tables, when there are more than one common fields between the two tables.
      So as i understand from your question, you have 2 tables and both of these tables have Date and Name fields.This is causing synthetic keys.
      You can have association using just one field so depending on your data model,you can decide between date or Name. If you decide on Date then you can rename ‘Name’ in one of the tables and that will resolve the synthetic key.
      You can still use Date and Name to create List boxes.

  3. Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your weblog? My blog is in the exact same area of interest as yours and my users would genuinely benefit from a lot of the information you present here. Please let me know if this alright with you. Appreciate it!

Leave a Reply

Your email address will not be published. Required fields are marked *