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

[MrBosch]

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

[marcus_sommer · Accepted solution]

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

[MrBosch]

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:

[marcus_sommer · Accepted solution]

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

[MrBosch]

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