Using the For Each loop to extract files from a folder

Picture a scenario where the month end sales data in an organization is stored in a folder on the network from where it needs to be picked up for reporting purposes.

Control statements such as For Each next can be used in Qlik Sense as an approach towards script iteration. The following recipe deals with extracting files in Qlik Sense from a folder, processing them to create QVD files and then transferring the source files to another folder. In the process, we will also deal with the incremental update of the QVD.

Getting ready

This recipe requires the Legacy mode to be activated. The steps are as follows:

  1. To activate the Legacy mode, open the Settings.ini file under C:Users<username>DocumentsQlikSense.
  2. Change the value of the StandardReload parameter from 1 to 0.
  1. For this recipe, we make use of four Excel files: January.xlsx, February.xlsx, March.xlsx, and April.xlsx. These files are provided with the chapter and can be downloaded from the Packt Publishing website.
  2. Save the file January.xlsx under c:QlikSense. If you are not able to write to this location, then you may change the storage location for the file. Note that in this case you will have to make relevant changes in the file location paths for the load script discussed in the How to do it... section for this recipe.
  3. Create another folder named Processed inside the QlikSense folder we created in step 1. The path for the folder would be c:QlikSenseProcessed.
  4. Create a third folder named QVD inside the QlikSense folder created in step 1. The path for the folder would be c:QlikSenseQVD.

How to do it…

  1. Create a new Qlik Sense application.
  2. Open the data load editor.
  3. Load the following script:
    For each File in filelist ('C:QlikSense*.xlsx') 

ProdSales:
LOAD
 left(FileBaseName(),18) AS ProdSalesFileName,
filename() as FileName,
 [Product],
[Sales]
FROM [$(File)]
(ooxml, embedded labels, table is Sheet1)
WHERE Sales >250000;

Execute cmd.exe /C move "$(File)" "C:QlikSenseProcessed";

next File

SET rowCount = 0;
LET rowCount = NoOfRows('ProdSales');

IF rowCount > 0 AND Alt(FileSize('C: QlikSense QVDProdSales.QVD'),0) > 0 THEN
Concatenate(ProdSales)

LOAD * FROM C:\QlikSenseQVDProdSales.QVD (qvd);

STORE ProdSales INTO C:QlikSenseQVDProdSales.QVD;

ELSE

STORE ProdSales INTO C:QlikSenseQVDProdSales.QVD;

END IF

DROP TABLE ProdSales;

LOAD * FROM C:QlikSenseQVDProdSales.QVD (qvd);
  4. Now, add the remaining three Excel files, that is, February.xlsx, March.xlsx, and April.xlsx, to the source location; in the case of this recipe, it is c:QlikSense.
  5. Load the script again. You will notice that all the files have been processed and moved to the processed folder. At the same time, the new data is appended to the ProdSales.QVD file.
  6. In order to test the data loaded into the QVD, go to App overview and create a new sheet.
  7. Drag a table object onto the sheet.
  8. Add ProdSalesFileName as the first dimension and label it Month.
  9. Add Product as second dimension.
  10. Add the following expression and label it as Sales:
    Sum(Sales)
  11. The resultant table would look like the following, with each month showing records only with Sales >250000:
    How to do it…

How it works…

The for each next loop iterates through each file in the Source folder and processes it to pick up records with sales greater than 250,000. Once processed, the files are transferred to the processed folder using the command prompt.

The if condition checks for the row count of the processed file. If it is greater than zero then the file is concatenated to the existing ProdSales.QVD file. The LOAD statement inside the if condition has a WHERE not exists clause which makes sure to append only new files to the QVD.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset