English Qlik Sense 2.2

Managing data table associations

You can fix association problems with data files that have been loaded in the data manager when you add data. Qlik Sense will associate tables based on common field names automatically, but there are cases where you need to adjust the association. For example:

  • If you have loaded two fields containing the same data but with a different field name from two different tables, it's probably a good idea to name the fields identically to relate the tables.
  • If you have loaded two fields containing different data but with identical field names from two different tables, you need to rename at least one of the fields to load them as separate fields.

Do the following:

  • Click Associations in the data manager overview.

Qlik Sense performs data profiling of the data you want to load to assist you in fixing the table association. Existing bad associations and potential good associations are highlighted, and you get assistance with selecting fields to associate, based on analysis of the data.

Viewing recommendations

Association recommendations are displayed in a list, and you can navigate between recommendations and warnings using the ˜ and ¯ buttons. If the list contains warnings, there are association issues that need to be resolved.

Two data sources contain a field with related data but with different names

In this case, the two tables contain a common field that is named differently in the two tables. Current shows that the tables are not associated, but Suggestions shows that there are two fields that contain similar data, with a high matching score.

To create an association between the tables, you need to rename one or both fields to a common name.

Do the following:

  1. Select the field pair that you find most suitable. This is usually the pair with the highest score.
  2. Select the field name you want to use, or enter a new custom field name.

The fields are now renamed to have the same name, and the tables will be associated when you load the data.

Two data sources contain fields with the same name but unrelated data

In this case, data profiling has revealed that the two tables contain fields with unrelated data, but with the same name, indicated by a low matching score. The ID field in the Sales table could be a unique identifier for every order, while the ID field in the SalesOrderHeader table is the identifier of the sales order header. If you load the tables without resolving the issue, the tables will be associated, which could result in a problematic data model.

To ensure that the data model is correct you have options to resolve the issue. You should choose the one which is the most accurate association. This is not necessarily the recommendation with the highest score in %.

  • Select a recommended association.
  • Break the association

Selecting a recommended association

In this example, there are two recommended associations based on profiling of the data, ID-RevisionNumber and ID-TerritoryID. If one of them is the correct association, you need to rename the associated fields to be the same.

Do the following:

  1. Select the recommended field pair .
  2. Select the field name you want to use, or enter a new custom field name.

The fields are now renamed and will be associated when you have loaded data.

Breaking the association

If the two tables are unrelated , but both contain a field with the same name you need to break the association.

Do the following:

  1. Select No association.
  2. Click Break the current association.

The fields are now renamed, by qualifying them with the table name, in this case Sales.ID and SalesOrderHeader. The tables will not be associated when you have loaded data.

Two tables contain more than one common field

If two tables contain more than one common field that would create an association, Qlik Sense would create a synthetic key. If this is encountered during data profiling, you will get recommendations to either keep one of the fields as a key by renaming the other common fields, or breaking the table association.

See: Synthetic keys

Undo and redo actions

You can undo and redo your association change actions by clicking B and C.

The undo/redo history is cleared when you close Associations.

Limitations

There are some cases where association recommendations are not provided, depending on the structure of the loaded tables and the data in the tables:

  • Many-to-many relationships are not recommended as associations.
  • If the data does not match well in both directions, the association is not recommended. This may be the case when you have a small table with a few field values that match a field in a large table 100%, while the match in the other direction is significantly smaller.
  • Compound key associations are not recommended.
  • The data manager will only analyze tables that were added with Add data. Tables added using the data load script are not included in the association recommendations.