Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator III
Creator III

Find the latest file and load it in the script

I have files in a folder, in the format below

20201207_05_31_06AM_Report.xls

20201208_13_31_06AM_Report.xls

20201209_17_56_31AM_Report.xls

20201209_22_31_15PM_Report.xls

The first characters are 20201207 in YYYYMMDD format while the latter ones are in HH_MM_SS  with AM or PM

How do I load data from .XLS in my script? In this case, it should be 20201209_22_31_15PM_Report.xls as that is the latest.

Labels (1)
1 Reply
Miguel_Angel_Baeyens

You can use first a FOR EACH file IN filelist ... NEXT loop and a string function to extract the date, then take the highest value from the list, then load the file.
I'm thinking something along the lines of
KeepChar(Mid(filenamehere, 1, Index(filenamehere, '_', -1)), '0123456789')
Then
LOAD Max(Value) AS FileToLoad RESIDENT ListOfFiles;
LET vFileToLoad = FieldValue('FileToLoad', 1);
LatestReport:
LOAD *
FROM $(vFileToLoad);

More on
- For Each: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...
- String functions: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Strin...
- FieldValue: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...