topic Re: LOAD * FROM any of the five file locations (folders) where the .xlsx file exists in QlikView

How to LOAD * FROM any of the five file locations (folders) where the .xlsx file exists in Qlik View?

MrBosch — Thu, 03 Nov 2022 14:21:56 GMT

Hi Qlik-ers

I have been reading a lot about this topic already but nowhere I found what I am actually looking for.

Consider: I have a source file data.xlsx in one of the following folders:

Folder1: C:\Users\XPS\Desktop
Folder2: C:\Users\XPS\Downloads
Folder3: C:\Users\XPS\Qlikview Dashboards
Folder4: F:\Qlikview Sources

I want the load script to check whether I have stored the data.xlsx file in any of these locations and then load it.

I tried many things: a FOR EACH loop or IF THEN's to determine the existence of the file then creating the variable usefile and use that to perform the LOAD. Unfortunately to no succes.

My last attempt for the load script:

IF FileSize('C:\Users\XPS\Desktop') > 0 THEN SET usefile='C:\Users\XPS\Desktop'; END IF
IF FileSize('C:\Users\XPS\Downloads') > 0 THEN SET usefile='C:\Users\XPS\Downloads'; END IF
IF FileSize('C:\Users\XPS\Qlikview Dashboards') > 0 THEN SET usefile='C:\Users\XPS\Qlikview Dashboards'; END IF
IF FileSize('F:\Qlikview Sources') > 0 THEN SET usefile='F:\Qlikview Sources'; END IF

LOAD * FROM [$(usefile)] (ooxml, embedded labels, table is Exports);

[I have not manually created a variable]

Any help would be highly appreciated.

Thanks

Re: How to LOAD * FROM any of the five file locations (folders) where the .xlsx file exists in Qlik View?

marcus_sommer — Thu, 03 Nov 2022 14:22:27 GMT

I would probably do something like this:

for each file in 'path1\data.xlsx', 'path3\data.xlsx', 'path3\data.xlsx', 'path4\data.xlsx' if filesize('$(file)') then t: load * from [$(file)] (ooxml, …); exit for; end if next

- Marcus

Re: LOAD * FROM any of the five file locations (folders) where the .xlsx file exists

MrBosch — Fri, 21 Jan 2022 13:18:33 GMT

Thanks Marcus,

I have changed the load script. Unfortunately it shows a fault somewhere and ignoring all other.

I have tried the ; after the file list. The IF becomes blue but still no luck.

With an additional ; after the file list:

Re: LOAD * FROM any of the five file locations (folders) where the .xlsx file exists

marcus_sommer — Fri, 21 Jan 2022 13:33:17 GMT

For/If loops are not load-statements else conditions-statements which don't accept a line-break within the condition-parts else it must be written within a single-line, just like in my example. If the line length creates horizontal scrollbars or if you want to limit the length to a certain value you need to replace the statement-parts with shorter variables or you load them within a table and looping them through this table with a simple for loop instead of a for each loop.

- Marcus

Re: LOAD * FROM any of the five file locations (folders) where the .xlsx file exists

MrBosch — Fri, 21 Jan 2022 14:54:11 GMT

Thank you very much Marcus. It works like a charm!