topic Re: Loading Multiple sheets from multiple folders in QlikView

Re: Loading Multiple sheets from multiple folders

QlikView_99 — Tue, 08 Aug 2023 11:27:14 GMT

I have a script which load all files in the subfolders in a given main folder, also have a separate script where multiple sheets in different files in a folder can be loaded.

Whereas the exact thing I'm looking for is how can we write a script that can load all the sheets from these different files which are in different folders.

Example: Lets say there are 2021, 2022, 2023 folders, each of the folder has different files Jan21.xlsx, Feb21.xlsl etc., in 2021 folder, Jan22.xlsx, Feb22.xlsx in 2022 folder and so on.

These files (xlsx one's) has different sheets in them by country, i.e., India, USA, UK, Germary etc., as different sheets in each file.

Now I need to load all the 3 years of files with all the countries coming up.

Re: Loading Multiple sheets from multiple folders

marcus_sommer — Tue, 08 Aug 2023 14:09:50 GMT

It's just a further loop against a dirlist(). Within the help is a great example of it:

For each..next | Qlik Cloud Help

Re: Loading Multiple sheets from multiple folders

cristianj23a — Tue, 08 Aug 2023 20:49:29 GMT

You can accomplish loading multiple Excel files across different subfolders in Qlik Sense using a combination of loops and wildcard characters. Here's an example script that you can use:

Here's an explanation of how this code works:

- The outer loop goes through the years 2021, 2022, and 2023.
- The middle loop goes through the months Jan, Feb, Mar, etc.
- The inner loop uses FileList to iterate through each Excel file in the specified Year/Month folder.
- Inside the inner loop, separate LOAD statements are used for each country's sheet (India, USA, UK, Germany).

You can adjust the folder paths and sheet names as necessary to match your file structure.

This will load all the specified sheets from all the Excel files across the folders into a single Qlik Sense table. Make sure that all the sheets have the same structure, or you may need to make adjustments to the LOAD statements to handle different formats.

Re: Loading Multiple sheets from multiple folders

QlikView_99 — Wed, 09 Aug 2023 06:17:42 GMT

Hi Marcus,

Thank you for having a look at it and I've tried writing a script to accomplish what I'm trying to and attached are the one's that might help you to resolve the issue here.

Re: Loading Multiple sheets from multiple folders

QlikView_99 — Wed, 09 Aug 2023 06:20:35 GMT

Hi Cristian,

Thanks for taking time to explain the scenario, but unfortunately the resolution you mentioned doesn't fit my criteria. I don't want to force the years and months but read them from the folders. I've added the working file, where I've tried to make it work. Please have a look at it.

Re: Loading Multiple sheets from multiple folders

marcus_sommer — Wed, 09 Aug 2023 07:02:16 GMT

What's the issue? Your script worked. Instead of just listing the files you could also load from them.

Just a few further hints. You should make your loading more explicitly by specifying to which table the load should be concatenated - means something like:

concatenate([myTable])

because otherwise the last loaded table would be taken.

Another point to keep attention for is that such for-loops and also external if-loop as well as sub-routines are control-structures which have a validity-workspace like it's common within many programming-tools. This means for example that a variable which is created within such area exists only there and is not globally available.

Re: Loading Multiple sheets from multiple folders

QlikView_99 — Wed, 09 Aug 2023 07:37:52 GMT

Marcus, my script attached above is executing but not working as expected. It's loading only few files and not everything. Could you explain the concat thing you've said a bit futher.

Re: Loading Multiple sheets from multiple folders

marcus_sommer — Wed, 09 Aug 2023 08:57:28 GMT

You are using the wrong iterator in the loop for fetching the sheets - it must j instead of i.

Re: Loading Multiple sheets from multiple folders

QlikView_99 — Wed, 09 Aug 2023 10:21:30 GMT

Thanks for your input. It's working.