English Exploring data Qlik Script

Split a table in multiple files (For loop) – Qlik Script

When you need to create separate files from the same table using a filter, this is a good way to go:

Let’s consider that we have a table with many countries in the Country column and you need to split this table creating one csv file for each country.

If you are new to Qlik the first thing to know is that we can filter the table in the script using the where clause, and also export the table using the Store command.

In the video above you will see the steps to export multiple tables using the following script as base:

// Creating a list of Countries without duplicate records
Countries:
LOAD distinct
    Area
FROM [lib://Source/FAOSTAT_data_6-23-2021.xls]
(biff, embedded labels, table is Sheet1$);


// If you want to export the table above, uncomment the store command below
// Store Countries into [lib://Source/CountryList.csv] (txt,delimiter is ';');


// Loop until the number of rows from the Countries table (-1) is reached
For i=0 to NoOfRows('Countries')-1

// Create a variable to filter the table below
// The peek functions considers: 1) Name of column  , 2) number of row , 3) Name of the table
Let vCountry = peek('Area', i , 'Countries' ) ;


MainTable:
LOAD
    "Domain Code",
    Domain,
    "Area Code (FAO)",
    Area,
    "Element Code",
    Element,
    "Item Code (FAO)",
    Item,
    "Year Code",
    "Year",
    Unit,
    Value,
    Flag,
    "Flag Description"
FROM [lib://Source/FAOSTAT_data_6-23-2021.xls]
(biff, embedded labels, table is Sheet1$)
where Area='$(vCountry)';


Store MainTable into [lib://Source/$(vCountry).csv] (txt, delimiter is ';');
//Drop the table otherwise the last file exported will contain records from all the other countries (they pill up)
Drop table MainTable;

// Next i to make the loop work
Next i

Leave a Reply

Your email address will not be published. Required fields are marked *