skip to content

In a Qlik load script, why do two tables sharing two field names produce a synthetic key?

level: middleimportance: should knowfreq 52%

answer

  1. there is no join syntax in the model
  2. the link is the field name itself
  3. one shared name is fine, two is not
  4. the engine refuses to guess which field you meant
  5. a generated table appears with $Syn in its name

basics

~20 s

Qlik associates tables automatically on identically named fields. With two names in common there is no single key to associate on, so the engine generates a hidden $Syn table holding the distinct combinations of those fields and links both tables to it.

solid answer

~50 s

Qlik has no join syntax in the data model: tables associate on **field names**. One shared name gives a clean association. Two or more shared names are ambiguous — the engine will not guess which one you meant — so it builds a **synthetic key**: a generated `$Syn` table containing every distinct combination of the shared fields, plus a generated `$Syn` key field in each original table pointing at it. The model still works and the numbers are often right, but a synthetic key almost always signals an unintended field-name collision, such as two tables both carrying `Date` and `ID`. It costs memory and reload time, and it makes the model hard to read. The fix is to make the association explicit: rename one field with `as`, or build a single composite key such as `AutoNumber(A & '|' & B) as %Key` and drop the components from one side so exactly one field name is shared.

code

text · 6 lines
text
// Orders:     OrderID, CustomerID, OrderDate, Sales
// Shipments:           CustomerID, OrderDate, ShippedQty
//
// Shared names: CustomerID AND OrderDate
// -> engine builds $Syn 1 holding the distinct (CustomerID, OrderDate) pairs
// -> both tables get a generated $Syn 1 field pointing at it

go deeper

for a junior

Know that Qlik links tables by matching field names, and that a table named with $Syn appearing in the data model viewer means two tables shared more than one field name.

for a middle

Explain the mechanism and the fix: the engine builds a generated key table of the distinct combinations, and you remove it by renaming a coincidental field or building one explicit composite key with the components dropped from one side.

for a senior

Diagnose it in a real model. Read the reload log and data model viewer after every script change, distinguish a harmless deliberate pair from a grain error that silently drops rows, and handle the circular-reference case with a link table or aliased calendars.

for a principal

Set the standard: naming conventions for key fields, a review step on script changes so a new source column cannot reshape the model silently, and a house pattern for role-playing dates rather than letting each developer invent one.

## Association is by name, and only by name When Qlik loads tables, it does not ask you to declare relationships. Any field name appearing in two tables becomes the link between them, automatically and implicitly. This is why the load script — not a modelling UI — is where a Qlik data model is designed, and why field naming discipline matters far more here than in tools where you draw relationships by hand. The consequence is that a name collision is a modelling decision you did not make. `Orders` and `Shipments` both innocently carrying `CustomerID` and `OrderDate` is not two associations; it is an ambiguous one. ## What the engine does with the ambiguity It does not pick a field. It creates a **synthetic key**: - A hidden table named `$Syn 1`, numbered per collision, containing the distinct combinations of the shared fields. - A generated field, `$Syn 1`, added to each of the original tables, holding a pointer into that table. The original tables now associate through the `$Syn` table rather than to each other. Aggregations still resolve, and for two shared fields the result is often exactly what you intended — the association on the *pair*. That is why synthetic keys are frequently described as "not always wrong". A small, deliberate one is tolerable. ## Why they are still a problem in practice - **They are usually accidental.** They appear when someone adds a column to a source table and its name happens to match. The model changes silently at the next reload. - **They multiply.** Three tables that pairwise collide can generate nested synthetic keys — a `$Syn` table participating in another `$Syn` — and reload time and memory grow with the number of distinct combinations, which can approach the row count of the larger table. - **They obscure the model.** Nobody reading the data model viewer can tell which of the shared fields carries business meaning. - **They often hide a genuine grain error.** If the real relationship is one field and the other collision is coincidental — two different meanings of `Date` — the pair-wise association silently drops rows that should have matched. The numbers are wrong, not just slow. ## The fixes, in order of preference 1. **Rename the coincidental field.** If `Shipments.OrderDate` is really the ship date, load it as `ShipDate`. One shared name remains, the model is explicit, and the synthetic key disappears. This is the correct fix more often than any other. 2. **Build an explicit composite key.** If the association truly is on the pair, make that intent visible: `AutoNumber(CustomerID & '|' & OrderDate) as %CustomerDateKey`, present in both tables, with the component fields dropped from one side so only the key name is shared. `AutoNumber` maps the concatenated string to a compact integer, which keeps the symbol table small. The `|` separator prevents different pairs collapsing into the same string. 3. **Join or concatenate.** If the two tables share a grain, `LEFT JOIN` or `CONCATENATE` them in the script so there is one table and no association to make at all. 4. **`Qualify`.** `QUALIFY *;` prefixes field names with their table name, so nothing associates by accident; you then `UNQUALIFY` the fields you *do* want to link on. Useful as a defensive default when loading unfamiliar sources, blunt as a routine tool. ## The related failure: circular references A sibling problem is the **circular reference** — three or more tables forming a loop through shared field names, so there is more than one path between two tables and the association is ambiguous in a different way. Qlik detects it at reload, warns, and marks one table **loosely coupled**, which severs it from selection propagation and quietly changes what your charts compute. The remedy is the same family of moves: rename a field to break the loop, split a dimension that is playing two roles — an order date and a ship date both pointing at one calendar table is the classic cause, solved with a link table or two aliased calendars — or concatenate fact tables into one. ## Reading the model The data model viewer shows `$Syn` tables explicitly, and the reload log reports the synthetic keys created. Reviewing both after every script change is the routine habit that keeps a Qlik model honest: a synthetic key that appears without a corresponding intent in the script is a defect to chase, not a warning to dismiss.

  • Is a synthetic key always a bug?
    No. When the association genuinely is on a pair of fields, the synthetic key produces the correct result, and a small one is harmless. The problem is that it is usually accidental — a coincidental name match introduced by a new source column — and it hides which field carries meaning. Even when correct, replace it with an explicit composite key so the intent is readable and the model cannot change silently at the next reload.
  • What is a circular reference and how do you resolve one?
    Three or more tables linked into a loop by shared field names, so two tables have more than one association path. Qlik warns at reload and marks one table loosely coupled, which detaches it from selection propagation and changes chart results. Break the loop: rename a field, use a link table, alias a calendar that two date fields both point at, or concatenate the fact tables into one.
  • Why wrap a composite key in AutoNumber rather than storing the concatenated string?
    The concatenated string lands in the symbol table as a distinct value per row, which is expensive in memory for a high-cardinality key. `AutoNumber` maps each distinct string to a sequential integer, so the model stores small integers instead. Keep a separator such as `|` inside the concatenation so different field-value pairs cannot produce the same string.

saying these in an interview costs you the question

  • Says you declare joins between tables in the Qlik data model
  • Claims a synthetic key means the reload failed
  • Thinks renaming both shared fields removes the association entirely
  • Confuses a synthetic key with a circular reference
  • Suggests setting a relationship cardinality, which Qlik has no concept of

context

resolving links…