This article will cover how to use the data load editor 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:
- Set app parameters
- Load data
- Rename fields and tables
- Add new fields
- Concatenate tables
- Join tables
- Associate tables
- Make other data transformations
When you are happy with the backend of the app, use the ‘Load data’ button to run the script. You can then start building sheets.
How to set app parameters
General app parameters are set on the first page within the data load editor. These are variables that define how Qlik displays data. You can change any of the parameters by editing the variable code.
How to load existing data into your app
When using the data load editor, can need to add a script part to load your required data.
Using the data connections sidebar in the data load editor, you can load data from the ‘data catalog’ (1) (Qlik Cloud only) or an existing data connection (3). Make sure the right space is selected when looking for an existing connection (2) (Qlik Cloud only).
When you go through the steps to load data, the final button will say ‘Insert script’. Clicking this will insert lines of code to the script editor which when executed will load your required 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:
- sales_truck1: a file containing sales data from one ice cream truck
- sales_truck2: a file containing sales data from another ice cream truck
- order_items: a file containing the total list of orders (from both trucks)
- 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 how by default, Qlik hasn’t given the tables a name. This will need to be addressed later on in the process. Also, note the syntax of the load statement. The base syntax is very similar to SQL, however, does change depending on the exact requirement and source of the data. Each statement does have to end with a ‘;’.
It’s important to be aware that field names are case-sensitive.
How to rename fields and tables
Renaming fields and tables is an important step in building an effective data model that can be used not only by the developer creating it but also by others in the future.
Naming tables
In a given app, table names have to be unique. If a name consists of multiple words, these cannot be separated by a space. Instead, an underscore is sufficient. To name a table, prefix the LOAD statement with the table name as such:
TABLE_NAME:
LOAD
*
FROM db path
Renaming fields
Renaming fields is important because it makes it easier to find the required field when building visualisations. It’s good practice to prefix a name of a field with the table name so that you can easily identify which table the field belongs to during sheet development. This is especially useful for large apps containing many tables and fields.
To rename a field you have to add the keyword ‘as’ after the field name. If your field name has spaces, wrap the name is double quotes or square brackets:
TABLE_NAME:
LOAD
field1 as "Table Name Field 1",
field2 as "Table Name Field 2",
field3 as "Table Name Field 3"
FROM db path
How to add new fields
Once you have the scripts to load your data, you may need to create new fields. To add a field, you use formulas in the table as required. In our example, we will add the ‘Truck ID’ field to the sales tables.
Note that the field was added below all other fields but this isn’t necessary. You can add new fields in any position. Because in this example a single identification number needed to be on every row in the table, there was no need for a formula.
How to concatenate tables
Concatenation may be required if you have multiple tables you want to join into one by stacking them on top. In our example, sales_truck1 and sales_truck2 are going to be concatenated to create a single data table containing all sales.
Concatenation is done by loading the first table to be concatenated (1). Then the second table is loaded with the keyword ‘concatenate’ prefixing the load (2). Note that the second table is not named. The final table name will be the first table’s name.
Note that the fields are identically named and are in the same order in both tables. Because of this, upon loading the data, Qlik would have automatically concatenated the tables. The keyword ‘concatenate’ only needs to be used when the field names are different but it is good practice to include it as it makes it clear to other developers that concatenation is the intended outcome.
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 Details’ table to the ‘Sales’ table so that all the truck information is contained in the ‘Sales’ table.
Joining tables is done by loading the first table to be joined (1). Then the second table is loaded with the correct keyword as a prefix (2). There are x4 join types allowed in Qlik: left join, right join, inner join and outer join.
Note that the second table is not named. The final table name will be the first table’s name.
Note that the fields in both tables on which the join is to be made are named identically. In this case, this is the field ‘Sales Truck ID’. Make sure that no other fields are identically named as this will result in a join being applied on incorrect fields.
As you can see, you can build a number of transformations sequentially. In this example, we first concatenate 2 tables and then do a left 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 in the data load editor, you need to name the fields to be associated identically.
Always make sure to check the data model viewer after reloading the data to make sure that the data model is as you intended.
Making other data transformations
Qlik Sense is a powerful tool for data transformations. There are a whole host of results you can achieve by transforming data. Each data model is unique and requirements will change depending on the outcome and the source data.
Explore the scripting syntax and script functions sections for ideas on how you can transform data.
