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.
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 ];
We will need to split the data stored in LineItems
into several records and, after that, split data into multiple columns.
- 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 precedingLOAD
:
Orders: LOAD OrderID, CustomerID...