Skip to main content

Synthetic keys

ON THIS PAGE

Synthetic keys

When two or more internal tables have two or more fields in common, this implies a composite key relationship. Qlik Sense handles this through synthetic keys. These keys are anonymous fields that represent all occurring combinations of the composite key.

When the number of composite keys increases, depending on data amounts, table structure and other factors, Qlik Sense may or may not handle them gracefully. For example, they may affect performance and increase memory usage. Whenever there are several synthetic keys that are dependent on each other, it is good practice to remove them.

Now it is time to load our final set of data.

Do the following:

  1. Open the Data load editor in the Scripting Tutorial app.
  2. Click Create new to add a new script section.
  3. Name the section Customers.
  4. Under DataFiles in the right menu, click Select data.

  5. Upload and then select Customers.xlsx. The data preview window opens.
  6. Select Sheet1
  7. Click Insert script.
  8. Click Load data.
  9. Now you can see in the data load progress window that a synthetic key was created.

    Data load progress window with synthetic key warning
    Data load progress window with synthetic key warning.

  10. Open the Data model viewer.
  11. We can see that a synthetic key has been created by seeing that a new table, $Syn 1 Table, has been created. It contains all the fields, Region and Region code, that the connected tables Sheet1 and Region have in common. In this case it makes the connections a bit confusing and misleading, so it is not desirable to keep.

    Data model viewer showing synthetic key
    Data model viewer showing synthetic key.

Resolving synthetic keys

The easiest way to eliminate synthetic keys is to rename one or more fields in the tables. This can be done when loading the data. Now we will go through the steps of how to remove a synthetic key.

  1. Open the Data load editor.
  2. Click the section Customers and delete the row in the LOAD statement saying:
  3. Region,

  4. Click Load data.
  5. Open the Data model viewer.
  6. The synthetic key is has been removed.

    Data model viewer showing that synthetic key has been removed
    Data model viewer showing that synthetic key has been removed.