Making associations in Qlik DataMarket

When data is loaded from a Qlik DataMarket data set, it is allocated to multiple individual tables. These tables are associated by generated key fields. Measures and time periods from the data set are consolidated in one table that is assigned the name of the data set. Dimension fields are allocated to individual tables. For example, the 3x3 currency exchange rates data set loads as three tables:

Some dimensions offer additional fields when loaded. The extra fields provide additional representations of the dimensions. In the 3x3 currency exchange rates data set, the currencies are also listed by the iso4217 representation. For example:

Data sets with population data by country and region offer extra dimension representations for the region names, such as ISO 3166 codes.

Associating a data set's multiple tables

All tables from a single data set should be associated through the measures table. When you load a data set from Add data, associations are made automatically from the generated key fields. The following data model shows the data set US population by state, race, sex and age. The dimension tables (Age, Sex, Location, and Race) are associated through the measures table, which has the data set name.

These associations are required to interpret relationships between the dimensions and the measures that are important in visualizations. For example, if a company wants to use the US population data to compare its product sales to age groups in various US states, the Age and Location dimensions must be associated through the measures table to get the number of people in each group in the various states.

When data sets have multiple dimension tables, there are often additional associations that can be made. For example, aggregate fields usually have the same value ("Total") that suggests a possible association. Such associations are not useful, however, and can result in circular references.

See: Understanding and solving circular references

The multiple-table structure increases the efficiency with which data is loaded, and it can improve the data associations.

Converting single-table data sets to multiple tables

Prior to Qlik Sense 3.0, DataMarket data sets were loaded as single tables. In Qlik Sense 3.0, those previously loaded tables remain as single tables, and any visualizations that use them continue to work as before. You can convert those single tables to multiple tables, and the visualizations will continue to work without modification. By converting them, you gain the efficiency of multiple tables.

Do the following:

1. Open Data manager.

2. Delete the table containing the DataMarket data set you want to convert to multiple tables.

   The table is marked Pending delete.

3. Click ú to add data.
4. Select Qlik DataMarket.

5. Select the data set corresponding to the table you deleted.

   When the Select data to load page opens, the selection you made previously is displayed. Do not change the selections unless you also plan to change the visualizations that use the data set. If you change the selections, existing visualizations that use the data set may no longer work.

6. Click ¥ beside the Add data button and then disable data profiling.

7. Click Add data.

   The data set is loaded in multiple tables and the single table is deleted.

Check the visualizations that use the data set you converted to multiple tables. They should work as they were originally designed unless you changed the data selected either by adding or removing some of the selections made when the table was loaded previously.