Loading latest file in folder

[#1]

Hi,

I have Oracle reports that are saved once a day on a local FTP server. I need to load the latest report based on the date the report was printed. I have written the following load statement;

sub readWIP(root)
for each File in filelist(root & '\*.txt')
tmpWIP:
LOAD @1:45 as wo_key,
if(@1:5='Date:',@13:21,peek('tmpReportDate')) as tmpReportDate,
@1:45 as tmpWO,
@242:260 as tmpStatus
FROM [$(File)] (fix, codepage is 1252, header is 1 lines);
next File
end sub

call readWIP('\\oer1euappw01\ftproot\reports\Discrete_Job_Value');

WIP:
Load wo_key,
tmpReportDate,
tmpWO,
tmpStatus
RESIDENT tmpWIP
WHERE tmpStatus = 'Released' and tmpReportDate = LATEST;

drop table tmpWIP;

---

Any ideas on how to write the code in bold or am I going about this entirely wrong?

Best Regards,
Jonas

[prieper · Accepted solution]

You should include the filename in the tmpWIP, then create a table with the filename and the latest date, take this into a variable and load this file.
Script might look:

sub readWIP(root)
for each File in filelist(root & '\*.txt')
 tmpWIP:
 LOAD @1:45 as wo_key,
 if(@1:5='Date:',@13:21,peek('tmpReportDate')) as tmpReportDate,
 @1:45 as tmpWO, @242:260 as tmpStatus,
FILENAME() AS FileName,
FILETIME() AS FileTime
 FROM [$(File)] (fix, codepage is 1252, header is 1 lines);
next File
end sub

call readWIP('\\oer1euappw01\ftproot\reports\Discrete_Job_Value');
xLastFile:
LOAD
FIRSTSORTEDVALUE(FileName, -FileTime) AS LastFile
RESIDENT
tmpWIP
GROUP BY
FileName;
LET sLastFile = PEEK('LastFile, 0, 'xLastFile');
LOAD * FROM $(sLastFile);

Alternatively you may also sort the tmpWIP by the date in descending order and pick with the PEEK-command the first filename in the first record

HTH
Peter