Simplify multiple files uploading

[jorgie]

Greetings to qlik community,

I would like to know if there is a way to upload identical tables from multiple files, without rewriting same code for each one of them, like the case below

Load
id,
value,
units
FROM [lib://folder/file_1.xlsx]
(ooxml, embedded labels, table is a)

Load
id,
value,
units
FROM [lib://folder/file_1.xlsx]
(ooxml, embedded labels, table is b)

Load
id,
value,
units
FROM [lib://folder/file_1.xlsx]
(ooxml, embedded labels, table is c)

Load
id,
value,
units
FROM [lib://folder/file_2.xlsx]
(ooxml, embedded labels, table is a)

Load
id,
value,
units
FROM [lib://folder/file_2.xlsx]
(ooxml, embedded labels, table is b)

Load
id,
value,
units
FROM [lib://folder/file_2.xlsx]
(ooxml, embedded labels, table is c)

Thanks in advance for your time!

//**  Communities are more effective and efficient than ChatGPT !!! **//

[henrikalmen · Accepted solution]

Yes, you can do it for example like this:

for each fileindex in 1, 2
    for each tbl in 'a', 'b', 'c'
        MyTable:
        Load id, value, units
        FROM [lib://folder/file_$(fileindex).xlsx]
        (ooxml, embedded labels, table is $(tbl));
    next tbl
next fileindex

[OmarBenSalem]

Please refer to this : 

https://www.linkedin.com/pulse/dynamically-load-multiple-sheets-from-excel-qlik-omar-ben-salem/

[jorgie]

thank you for your time!

I must admit that it was a bit complicated for me as a solution, i was not able to understand it.

Still thanks again for your time and for willing to help!

[henrikalmen]

"fileindex" and "tbl" are new variables.

The for ... next statement creates a loop, iterating over the values. So first the variable fileindex is assigned the value 1, and the variable tbl is assigned the value 'a'. In the load statement, these values are referenced with $(fileindex) and $(tbl). You can see it as $(fileindex) being replaced with the number 1 in the first iteration of the loop. The next command tells the loop to restart at its for-statement, until every value in the list (1,2 for fileindex, and a-b-c for tbl) has been processed.

[jorgie]

thank you a lot for your time and surely for solving the issue that I had!