Transforming data
You can transform and manipulate data using many different techniques in the data load editor.
One of the advantages to data manipulation is that you can choose to load only a subset of the data from a file, such as a few chosen columns from a table, to make the data handling more efficient. You can also load the data more than once to split up the raw data into several new logical tables. It is also possible to load data from more than one source and merge it into one table in Qlik Sense.
In this topic, you will perform some basic data transformation using a
Resident LOAD
You can use the
In this example, you will create a new table called Sales_Buckets and then load the data from Table1 using a resident load. In the Sales_Buckets table, you will create a variable called quantity_threshold, and then use a
- Open the Data load editor in the Scripting Tutorial app.
- Click the Sales tab.
- Add the following to the end of your script:
- Click Load data.
- Open the Data model viewer. You can see that you created a new table called Sales_Buckets with the data loaded according to the fields that you specified, and the threshold that you set.
- Add the data to a table in your app. Add Item and Customer as dimensions. Add High-Quantity as a measure aggregated on Count, and then again aggregated on Sum. Next, add a new column as a measure with the following formula:
- Now that we have completed this example, comment out the script for the quantity_threshold variable and the Sales_Buckets table.
SET quantity_threshold = 12000;
Sales_Buckets:
LOAD
"Sales Qty" as "High_Quantity",
"Item Description" as "Item",
"Customer Number" as "Customer"
Resident Table1
Where ("Sales Qty" > $(quantity_threshold));
Your script should look like this:


= Sum(High_Quantity) / Count(High_Quantity)

Your table shows, for example, that Customer 10025737 has made 4 large orders of High Top Dried Mushrooms, with an average quantity of 14,800. To perform sorts on the data in the fields, close Edit mode by clicking Done.

The end of your script should now appear as follows:

Preceding LOAD
A preceding load allows you to perform transformations and apply filters so that you can load data in one pass. Basically, it is a
As mentioned earlier in this tutorial, you can load data into Qlik Sense using the
This example is not related to the data we are loading in this tutorial. It is only used to show an example of what a preceding load can look like. You will create an inline table in the data load editor called Transactions. Date interpretation will be performed in the preceding
- Create a new app and call it ReformatDate.
- Open the data load editor, and then create a new tab called TransactionData.
- Add the following script:
- Click Load data.
- Open the Data model viewer. Select and expand the Transactions table. You can see that all the fields were loaded as specified by the * in the preceding load statement. A new field called transaction_date was created. The field has the reformatted date.
Transactions:
Load *,
Date(Date#(sale_date,'YYYYMMDD'),'DD/MM/YYYY') as transaction_date;
Load * Inline [ transaction_id, sale_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, m, orange
3752, 20180916, 5.75, 1, 5646471, S, blue
3753, 20180922, 125.00, 7, 3036491, l, Black
3754, 20180922, 484.21, 13, 049681, xs, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black ];
Your script should look like this:

