When two or more internal tables have two or more fields in common, this implies a composite key relationship. QlikView handles this by creating synthetic keys automatically. These keys are anonymous fields that represent all occurring combinations of the composite key.
If you receive a warning about synthetic keys when loading data, it is recommended that you review the data structure in the table viewer. You should ask yourself whether the data model is correct or not. Sometimes it is, but often enough the synthetic key is there due to an error in the script.
Multiple synthetic keys are often a symptom of an incorrect data model, but not necessarily. However, a sure sign of an incorrect data model is if you have synthetic keys based on other synthetic keys.
Handling synthetic keys
If you need to avoid synthetic keys, there are a number of ways for solving this in the load script:
-
Check that only fields that logically link two tables are used as keys.
- Fields like “Comment”, “Remark” and “Description” may exist in several tables without being related, and should therefore not be used as keys.
- Fields like “Date”, “Company” and “Name” may exist in several tables and have identical values, but still have different roles (Order Date/Shipping Date, Customer Company/Supplier Company). In such cases they should not be used as keys.
-
Make sure that redundant fields aren’t used – that only the necessary fields connect. If for example a date is used as a key, make sure not to load year, month or day_of_month of the same date from more than one internal table.
-
If necessary, form your own non-composite keys, typically using string concatenation inside an AutoNumber script function.
