Load latest csv file

[tsoley9262]

I am using the following script to run daily and load the csv file with today's date in the file, I need to know how if there is a day the file is not updated with todays date how i keep the last file.

Let vToday=date(today(), 'YYYY-MM-DD');

LOAD [UCS Domain] as 'CAV CS Domain',
Org as CAVLOB,
[Host Name] as CAVHostName,
[Hardware Status] as CAVHardwareStatus,
[SAN Name] as CAVSANName
FROM
[\\cnjxcarlafpd02p.svr\san$\SAN-Fabric-Refresh\VSI-Pending-Reboots\AP-HK-Cavendish\hkcdhvsi.pending-reboots.$(vToday).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[m_woolf · Accepted solution]

Maybe something like:

let vDir = 'Your File Path';

// Get latest file name
Temp:
load
FileBaseName() as FileName
from [$(vDir)\*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

MaxDate:
NoConcatenate
load
max(FileName) as MaxDate
resident Temp;
drop table Temp;

let vFileName = date(peek('MaxDate',-1,'MaxDate'),'YYYY-MM-DD');
drop table MaxDate;

Data:
load
whatever
from [$(vDir)\$(vFileName).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);