Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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:
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
Thank you very much Marcus. It works like a charm!