Load data from Excel files based on the file name

[#1]

Hello.I hope someone can help me on this. I have a folder with excel files. The file names are Sales123, Sales789 etc. I want Qlikview to load the excel files according to the 3 numbers in the filename,which represent the branch. I create a for loop which works fine:

for Branch=123 to 789

load Results from Sales$(Branch).xlsx (ooxml, embedded labels, table is Sheet1);

next

The problem is that the number of branches may change, so i have to load the branch names from another excel file which has 2 columns, BranchNo (1,2 etc),Branch (123,789 etc). How can i pass that parameter in the for loop? Thanks in advance

[#2]

You can simply place all the branch files in a folder called 'branchfolder' and folder, drop the for next loop and use

load
subfield(Filename(),'.',1) as [BranchFromFilename],
Results
from branchfolder\Sales*.xlsx (ooxml, embedded labels, table is Sheet1);

and if your branch files have the branchname/number already in a field you dont even need the subfield line!

[sparur · Accepted solution]

If I right understood, you can use a map table for according branchNames.

1) create a map table:

BranchMAP: MAPPING LOAD BranchNo, Branch FROM Excel2.xls...

2) use it map for creating excel filename

for i=1 to 10 (10 - it' example)

LET vFilename = 'Sales' & ApplyMap('BranchMAP', $(i));

load Results from $(vFilename).xlsx (ooxml, embedded labels, table is Sheet1);

next

[#4]

Thanks for the reply. The reason i use the for loop is that i want to load the excel files in groups, not all at the same time. What if the excel file that contains the branches has a column called group (A,B etc) and i want to load only the branches of group A? Thank you

[#5]

Thank you again! That is exactly what i need.

[#6]

It works ok, but i have another question. What can i do if the excel file with the branches has a column named 'group',with values(A,B etc), instead of BranchNo and i want to load only the files from the branches in group A?All the files have to be in the same folder.Thanks again.