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!
