Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!
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
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
Thank you again! That is exactly what i need.
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.