Loops in the Script

Sometimes a straight forward load script will just not do the job!

 

What happens if you want to take multiple data sources from one or more locations, pull them into the same place and apply the same logic to the data

 

An example of this is connecting to Google AdWords for multiple companies or pulling multiple companies data from Sage50

 

To do this there are a few options.

 

Wildcards

 

If your data source has a standard naming convention, such as a network drive full of budgets for each month, you can wildcard the name in the from statement to pull all files of the same name. Qlik automatically concatenates all tables with the same structure, therefore you would get one table with all data. To separate the rows you can then put a Filename()  function to make sure each file is present. This would look like the below:

 

Budget:

LOAD

FileName() as FileName,

Division,

Budget

FROM [lib://Folder/*Budget.xlsx]

(ooxml, embedded labels, table is Sheet1)

 

 

Loops

 

If you have multiple locations/connections you can then use loops. There are a few different types of loops

 

For Loops

 

For loops work on a numerical value that increments on each loop. A simple example of this would be the below script. In this script a variable called “vCompany” stores a numerical value which would then increments when it reaches the “next vCompany” section of the script. This would then load three excels for company 01 to company 03

 

for vCompany = 1 to 3

 

Companies:

LOAD

Company,

Data

FROM [lib://DSK/Company 0$(vCompany).xlsx]

(ooxml, embedded labels, table is Sheet1);

 

next vCompany;

 

 

For Each Loops

 

What happens if the value you want to loop on is not numeric?

 

For Each loops work where you specify the values to loop through. In the script below we vary the for loop to work for each listed value in the “For Each vCompany in 'Company 01', 'Company 02'” statement. The same next statement as in the for loop would then increment the company value per each iteration.

 

 

For Each vCompany in 'Company 01', 'Company 02'

 

Companies:

LOAD

Company,

Data

FROM [lib://DSK/$(vCompany).xlsx]

(ooxml, embedded labels, table is Sheet1);

 

 

next vCompany;

 

 

For Each in a Table Loop

 

The for Each Loop works well but you  need to type in each value. If you then had 100 companies this would soon become unmanageable. We can adapt the above to read a list of companies from a table and loop through the list. In the below you can see that the first load reads in a table of companies from an excel list.

 

The script then creates a variable called vCompanyRows. This variable holds the number of rows in the list.

The iCompanyID variable creates a numerical figure to loop through and sets the maximum iteration to be the number of rows in the list.

The vCompany variable is created to peek back into the company list, looking at the row number held in the CompanyID variable and return the company name. This is then passed to the load script to create the connection and would change name based of the row number per iteration.

 

 

//Load list of files

LoopList:

LOAD

CompanyList

FROM [lib://DSK/Loop List.xlsx]

(ooxml, embedded labels, table is Sheet1);

 

 

//Load List of files and get the number of rows. Add 1 as starts at 0

vCompanyRows = (NoOfRows('LoopList') -1);

 

//set the company id to be 0 to number of rows

For iCompanyID = 0 to $(vCompanyRows)

 

//loop through and peek into the list based on row number to get each listing and store the name in variable

Let vCompany = Peek('CompanyList',$(iCompanyID),'LoopList');

 

 

Companies:

LOAD

Company,

Data

FROM [lib://DSK/$(vCompany).xlsx]

(ooxml, embedded labels, table is Sheet1);

 

next iCompanyID;

 

Drop table LoopList;

 

Hope this helps!

Leave a Reply

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