Incremental load by file names in the folder

[Nata1739]

Hello everyone!

I`m trying to make incremental load by file names in the folder. The file names consists of dates - 060322, 070322 etc.

For example,  max date in qvd file is 050322. So, I need to load only the following files from my folder : 060322, 070322.
My script (please see below) does not work correctly - it does not apply this condition: if date(date#(FileBaseName(file),'DDMMYY'),'DDMMYY') > vMaxDate

Could you please advise what is the issue and how to correct it?

Thank you in advance!

[Max_table]:

LOAD

    max(date(day, 'DDMMYY')) as MaxDate

FROM [lib://folder_Mail/Mail.qvd]
(qvd);

Let vMaxDate = MaxDate;

[Total]:

LOAD *
FROM
[lib://folder_Mail/Mail.qvd]
(qvd);

 For Each file in  FileList('lib://folder_Mail/*.csv')

if date(date#(FileBaseName(file),'DDMMYY'),'DDMMYY') > vMaxDate Then

 LOAD *          
FROM $(file)
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

End if;

Next file;

[rubenmarin · Accepted solution]

Hi, you can check in the logs how the values are converted, or use the TRACE sentence to check values while loading, or do a debug reload to check the variable values after each step.

I think the max in max(date(day, 'DDMMYY')) is making the value to be stored as number( ie. 44629 instead of 090322). Also you cant refer to a field just calling it, you need to use Peek or Lookup.

Another thing is that using an operator like > with DDMMYY format may be wrong, it should be YYMMDD. As an example 300122 is higher than 090322. If you change to YYMMDD 220130 is lower than 220309.

And lastly the FileBaseName() function don't expect a parameter, it returns the filename of the file beign loaded, so it only works on LOAD. Try extracting file name from the file variable.

So try to assign the variable as

Let vMaxDate = Date(Peek('MaxDate',0,'Max_table'),'YYMMDD');

And the condition to:

LET vFileName = Subfield(Subfield('$(file)','\',-1),'.',1); // keep the text between the last \ and the dot.
LET vFileDate = Right('$(vFileName)',2) & Mid('$(vFileName)',3,2)&Left('$(vFileName)',2);

if $(vFileDate) > $(vMaxDate) Then

[Nata1739]

Hello Ruben!
Thank you very much for your advises!

Especially for Trace, I did not know that it can be used for script debugging.