Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
MrBosch
Contributor III
Contributor III

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

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

2 Solutions

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor III
Contributor III
Author

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.

load script.jpg

With an additional ; after the file list:

load script2.jpg

 

 

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor III
Contributor III
Author

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