Max Date from Files Name

[Martin22]

Hi everyone,

I would like a little help for this case :

I load a bunch of QVD files in my app. Those QVD files are named DATA_YYYYMM (ex : DATA_202010).

Until now, to load only a specific historic, I used this in the script :

LET vDateMin = Date(ADDMONTHS(Today(),-3),'YYYYMM');
LET vDateMax =Date(ADDMONTHS(Today(),-1),'YYYYMM');

FOR i = $(vDateMin) to $(vDateMax)

IF RIGHT(i,2) = 13 THEN
i = LEFT(i,4) + 1&01
END IF ;

(note : this part is only to handle the end of a year)

LET vFilePath='lib://Path\DATA_$(i).qvd';

TABLE :
LOAD
*
FROM
'$(vFilePath)'
(qvd);

NEXT;

What I would like to do now is to let vDateMin as it is, but to have for vDateMax the date of the last file created in the repertory, with the most recent YYYYMM.

I tried this :

FOR EACH vFile in FileList('lib://Path\DATA_*.qvd')
LET vDateMax = Max(Right(SubField(vFile, '.', 1), 6));

But no value appears, and the script ends in error.

Can you help ?

Best regards,

Martin.

[GaryGiles · Accepted solution]

The list appears to return in sorted order, so if you change the vMaxDate definition to 

FOR EACH vFile in FileList('lib://Path\DATA_*.qvd')
LET vDateMax = Right(SubField(vFile, '.', 1), 6);

NEXT

you should end up with the most recent YYYYMM.