Find the latest file and load it in the script

qlikwiz123 — Fri, 15 Nov 2024 23:28:55 GMT

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.

Re: Find the latest file and load it in the script

Miguel_Angel_Baeyens — Wed, 09 Dec 2020 14:45:15 GMT

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/ScriptControlStatements/For%20Each.htm
- String functions: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/StringFunctions/string-functions.htm
- FieldValue: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/InterRecordFunctions/FieldValue.htm