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