Using the Data Manager to Load and Transform Data

This article will cover how to use the data manager to load and transform data in a new app.

Once you have considered your data model requirements, you can begin building your app. This article will use sample data from an imaginary ice cream truck company.

Several steps need to be completed to successfully load data into an app and build the data model. These steps are not all necessary and do not have to be completed in the order specified here. Whether a step is relevant and the order in which you will complete them will change depending on your app requirements. The steps are as follows:

  • Load data
  • Add new fields
  • Concatenate tables
  • Join tables
  • Associate tables
  • Rename fields and tables for clarity
  • Make other data transformations

When you are happy with the backend of the app, use the ‘Load data’ button to run the script. Then, check the data model viewer to make sure the data model is as it was intended. You can then start building sheets.

How to load existing data into your app

The first step is to load the required data. Click ‘Add data’. If you’re using Qlik Sense in Qlik Cloud, you will see an option to load the data from the ‘Data catalog’ or from ‘Files and other sources’. The ‘Data catalog’ option will not be available in on-premise products. Choose whichever option is suitable for your data. You may need to create a new data connection if you require one. In this article, we will load x4 Microsoft Excel files as follows:

  1. sales_truck1: a file containing sales data from one ice cream truck
  2. sales_truck2: a file containing sales data from another ice cream truck
  3. order_items: a file containing the total list of orders (from both trucks)
  4. item_details: a file containing detailed data for items in each order

We will also create a new table by manual entry which will serve as a lookup table for details of the two trucks. Note that when you load data, this data will be displayed as a data table.

Note that if you are adding data from Excel files, the sheet names will be brought into the app. If there are duplicate sheet names across files, a number will be added to each to differentiate between them. To change the sheet names, click on the pencil icon. Then click on the sheet name in the top left corner of the table edit view and change the name.

How to add new fields

Once you have data loaded, you may need to create new fields. To add a field, go into the table edit mode for the table to which the field will be added. Click ‘Add field > Calculated field’. You will now see a new sidebar on the right where you can input the required field details. You can enlarge this window if your formula is complicated so that you can see it better.

The sidebar consists of the following:

1. The field name

This is where you enter the name of the field as it is to appear in the table.

2. The expression editor

This is where you enter the functions required to create your new field. In our example, we are simply adding the truck ID of 1 to this sales dataset so that we can join the truck 1 details to the dataset later on.

3. Expression helper options

The options here help you write your function. The first button brings up a list of all functions possible. They are grouped by function type. Clicking on one will insert it into the expression editor. Learn more about functions here.

The second button brings up a list of fields available in the table which will be inserted into the expression editor if you click on one. This is so that you can choose the field to which you are applying the function.

The third button shows a list of operations from which you can choose if you want. 

4. The preview

The preview shows a preview of the new calculated field values.

5. The create button

Once you are happy with your field, click this button to add the new field to the table. It will show in the table after all the other fields.

How to concatenate tables

Concatenation may be required if you have multiple tables that you want to join into one by stacking them on top of each other. In our example, sales_truck1 and sales_truck2 are going to be concatenated to create a single data table containing all sales.

To begin the concatenation process, click the first table that will be part of the process and click ‘Concatenate or join’. 

Now, click ‘Select action > concatenate’ and click on the second table you want to be concatenated.

The view now shows the resulting table. The top row contains the fields from the first table and the second row has the fields from the second table. By default, Qlik attempts to fit fields from table 2 into table 1. This is called mapping. If you are not happy with where the fields are being placed, you can use the button ‘Edit mappings’ (1) which will make the fields in table 2 draggable. You can drag the fields to the correct place (2).

You can also edit the table name and field names for the final resulting table (3).

Once you are happy with the new table, click ‘Apply’.

Observe that the tables you concatenated are no longer shown in the tables view. The new concatenated table is shown and it has a number icon to show how many tables make the table up. To undo the concatenation, click on the double arrow icon next to the edit pencil icon to split the table up. 

How to join tables

Joining tables may be required if you want to combine two or more tables into one wide table by adding fields from a table as new fields to another table. In our example, we will join the ‘Truck info’ table to the ‘Sales’ table so that all the truck information is contained in the ‘Sales’ table. 

To join tables, click on the table to which you are joining and click ‘Concatenate or join’. Click on ‘Select action’ and from the drop-down select the appropriate type of join.  Then click on the table to be joined and observe the default Qlik mappings. 

If you aren’t happy with the mappings you can click ‘Edit mappings’ (1) and drag the fields into the correct places (2). Note that when performing a join, the fields on which you are joining the table must be stacked on top of each other. You can change the table and field names if required before you ‘Apply’ the join. 

How to associate tables together on specific fields

If your data model requires multiple tables, associating them correctly in the data model is crucial to make the app function as intended. 

To associate tables, you need to the associations view. The associations view shows the tables as bubbles.

Once in the associations view (1), you can drag a table on top of another table to indicate to Qlik that you want the two tables to be associated.

When tables are associated, you can see a line connecting the bubbles. If you click on the connection between the tables, you can see the fields that they are associated on. If these are not the correct fields, you can correct this. 

How to change the association fields

To change Qlik’s default association fields, click on the button showing the field names.

You can now select a combination of suggested fields or opt for a custom association (1). Clicking ‘Custom association’ will launch a pop-up where you can choose a specific field from both tables to use for the association. There is also the option to use a delimiter on the fields if you need to split values.

You can change the association name if needed (2). 

At the bottom, you can see a preview of the two tables. The currently associated fields have grey rows. In our example, we will change the association to be on the ‘Sales ID’ and ‘Order ID’.

Note that you can associate multiple tables together. Just drag and drop the next table on top of an already existing association and go through the process again.

How to rename fields

Depending on what your source data is like, you may want to change the field names to make it clear what each field shows when you’re building the visualisation in the sheets.

To edit the field names, go into the table editor. Double-click on the field name and you will be able to type a new name in there. 

Other transformations possible in the data manager

There are several other options available in the data manager for transforming data. These include:

  • Unpivot
  • Filtering data on load
  • Changing the default field type
  • Changing the default field classification
  • Replacing values in a field
  • Setting values to be read as NULLs
  • Create value buckets
  • Order values
  • Split values

Unpivot

The unpivot option allows you to create a long table instead of a wide table. It switches rows into columns. To use unpivot, go into the table editor and click ‘Unpivot’ (1).

You will now need to select which fields you want to apply ‘unpivot’ to. Click on the fields to select them. This will highlight the grey area above the field name in orange (2). The preview of the new table is shown at the bottom. Observe the unpivoted fields (3). The field names are now a separate field that is called ‘.Attribute field’ and contain the field names as the values. The other new field is called ‘.Data field’ and contains the values from the unpivoted fields. Note that this process will add new rows to the table and the existing table ID field will be duplicated (4).

Filtering data on load

Filtering data on load means restricting the rows/ columns from your underlying dataset that will be brought into the app. For example, you may have a dataset for 10 years but you only want the last 1 in the Qlik app. Filtering data is done when loading data in.

When you select a data source, you will see a screen similar to this. Note that it may look slightly different, depending on the source type.

This here is an Excel file. When in the load view, you can use the toggle to switch between the table view and filter view (1). In the table view, you can restrict which tables/ sheets in the source you will bring in by unticking the ones not needed (2). In the data preview, you can untick certain fields to not bring them into the app (3).

While in the filter view, you can click ‘Add filter’ to add a new filter to the data. You will need to choose the field out of the list of fields in the data source, the condition and the value. The conditions available are: =, >, <, >=, <=. The value will need to be manually typed in. Note for string filters that this is case-sensitive. 

Changing the default field type

In the table editor, there is a button to change the field type for each field. By default, Qlik may have set the field type as ‘General’. 

By clicking the square icon above the field name in the table editor, you can change the type. There are only 4 options available: ‘general’, ‘date’, ‘timestamp’ and ‘geo data’. If you select any option other than ‘general’, you will get additional options. For example, selecting ‘date’, allows you to change the default display format of the field. This is useful when the field is displayed in visualisations.

Changing the default field classification

The field classification defines whether Qlik treats a field as a dimension or a measure. The transformation options available change slightly based on the field classification. In the table editor, when you click on a field you get some summary statistics about the values in the field at the bottom of the screen.

In the summary statistics box on the left, you can use the drop-down next to the field name to change the classification (1). On the right, you will see options for field transformations. These will vary based on the field classification. 

Replacing values in a field

Sometimes, your data may not be as complete or accurate as you want it to be and to fix it, you need to replace certain values in a field with other values. Replacing values is field specific but can be done in fields of any classification.

Replacing can be done in the table editor. Click on the field you want to replace values in. At the bottom of the screen, on the right, there is a transformation box. One of the options is ‘Replace’. 

You can choose a value from a list of distinct values in the field by clicking on it. Then, on the right, you can enter a new value.

You can make multiple replacements. 

To delete a replacement, click on the ‘x’ button next to the value. 

Setting values to be read as NULLs

Qlik Sense is designed to ignore NULLs from the analysis. For example, if you have a measure with 0 values and want to calculate the average, the 0s will be used for the calculation. If those 0s were set to be NULLs, they would be ignored. For this reason, if your underlying data doesn’t show NULL values where you want it to, you can get Qlik to treat specific values as though they were NULLs.

This is field specific but can be done on fields of any classification. 

In the table editor, click on the field required. In the bottom right of the screen toggle the ‘Set nulls’ tab. Click on the values in the field that you want to be treated as NULLs. 

Create value buckets

Value buckets are a way to group values in a measure field into ranges. Using this option adds a new dimension field to the table where the value ranges defined are the field values. Note that buckets can only be defined where there are many non-distinct values. 

Go to the table editor and click on a measure field. In the bottom right of the screen, toggle the ‘Bucket’ option (1). 

Define how many buckets you want your values to split into (2).

Use the scale at the bottom to set the bucket ranges (3). Observe the data preview in the centre. The x-axis shows the bucket ranges and the y-axis shows a count of how many values in the field fall into each bucket.

When you are happy with the buckets, click ‘Create buckets’. This will add a new dimension field to the data table.    

Order values

Ordering values can help you display data in your visualisations in a specific order. It can only be done on dimension fields.

Ordering items can be done in the table editor. Click on the field you want to order. At the bottom right of the screen, toggle the ‘Order’ option.

You will see a list of distinct values in the field and a preview of how many entries there are of each.

Drag the distinct values up or down to change their order. Click ‘Reorder’ when done. 

Split values

Splitting values is a way to split a single dimension field into multiple fields.  For example, you may have a field containing names and you want to split it into 2 fields, one containing the first name and the other the last name. This is the option you could use for this.   

Splitting values can be done in the table editor. Click on the field required. You can choose the field to use to define the split using the drop-down.

Click here and drag the yellow box to select a position or delimiter required. You will get the option to split there for:

  • this instance: split at only this specific place. For example, if you are splitting on a specific space but there are 2 spaces in the field, this is the option you want.
  • all instances: split at all places this delimiter exists. For example, if you have multiple spaces in fields and you want to split at each space, this is the option you want. 
  • these positions: this will split all values at the exact count of characters you placed the delimiter

Observe the preview at the bottom and click ‘Create fields’ if you are happy with the split.