Using the subfield() function to split field into multiple records

Sometimes, we have nested data stored in a field, instead of several lines to save space in a database. This is manageable for storage and record-by-record retrieval. However, for analysis, we need to split data into multiple records. For this purpose, we use the subfield() function.

Getting ready

Create a new Qlik Sense application and load the following script into your Qlik Sense application:

LOAD * INLINE [ 
OrderID, CustomerID, LineItems 
200, 1, Gloves;100|Helmet;75 
201, 2, Raincoat;50|Gloves;70|Seat;90 
202, 3, Seat;80 
203, 4, Mudguard;90 
]; 

How to do it…

We will need to split the data stored in LineItems into several records and, after that, split data into multiple columns.

1. Load the following script to generate the records for each order item, and separate the product name and sale value data into two new columns. You can add this on top of the initial LOAD as a preceding LOAD:

Orders: 
LOAD 
    OrderID, 
    CustomerID...