First file in QVD

anriretief — Tue, 18 Sep 2018 08:10:19 GMT

I am trying to achieve the following:

I need to create a variable that will represent the first day a new file was put in the folder in September of each year (this will not necessarily be 1 September but rather the first date in September that a new file was put in the folder from which I load the data)

The date is included within the file name, for instance 'TB180911' (this will be the 11th of September 2018)

I have tried to do this withing the 'for each... in filelist' part of the code , but this is not working seeing as it includes all files as it loops through the filelist

I would appreciate any help to solve this problem

Thanks in Advance

Below is the Code I am using:

for each filecurrent in filelist ('D:\DATA\Hoofraam\TB*.CSV');

tmpStart:

load

if(left(right(filebasename(),4),2) = 9 , min(right(FileBaseName(),2))) as Start

From

[$(filecurrent)]

(fix, codepage is 1252);

let Start = peek('Start',0,'tmpStart');

drop table tmpStart;

let FinCurrent = if(mid(right(subfield('$(filecurrent)','.',1),6),3,2) < 5, left(right(subfield('$(filecurrent)','.',1),6),2)-1 & left(right(subfield('$(filecurrent)','.',1),6),2),

if(mid(right(subfield('$(filecurrent)','.',1),6),3,2) >=5, left(right(subfield('$(filecurrent)','.',1),6),2) & left(right(subfield('$(filecurrent)','.',1),6),2)+1));

let filedate = right(subfield('$(filecurrent)','.',1),6);

let finstart = left(right(subfield('$(filecurrent)','.',1),6),2) & 09 & '$(Start)'; // eerste datum van die finansiele jaar

let finend = left(right(subfield('$(filecurrent)','.',1),6),2)+1 & 0430; // laaste datum van die finansiele jaar

if '$(filedate)' = '$(finstart)' then // wanneer die file datum gelyk is aan die eerste dag van die finansiele jaar en groter as 1819 fin jaar

CurrentTemp:

load *

FROM

[$(filecurrent)]

(fix, codepage is 1252);

end if

STORE CurrentTemp into "D:\DEV\Anri\TOETS\Takbuffer$(FinCurrent).qvd" (QVD);

next

for each filecurrent in filelist ('D:\DATA\Hoofraam\TB*.CSV');

tmpStart:

load

if(left(right(filebasename(),4),2) = 9 , min(right(FileBaseName(),2))) as Start

From

[$(filecurrent)]

(fix, codepage is 1252);

let Start = peek('Start',0,'tmpStart');

drop table tmpStart;

let FinCurrent = if(mid(right(subfield('$(filecurrent)','.',1),6),3,2) < 5, left(right(subfield('$(filecurrent)','.',1),6),2)-1 & left(right(subfield('$(filecurrent)','.',1),6),2),

if(mid(right(subfield('$(filecurrent)','.',1),6),3,2) >=5, left(right(subfield('$(filecurrent)','.',1),6),2) & left(right(subfield('$(filecurrent)','.',1),6),2)+1));

let filedate = right(subfield('$(filecurrent)','.',1),6);

let finstart = left(right(subfield('$(filecurrent)','.',1),6),2) & 09 & '$(Start)';

let finend = left(right(subfield('$(filecurrent)','.',1),6),2)+1 & 0430;

if right(subfield(filecurrent,'.',1),6)> vLastReload and '$(filedate)' > '$(finstart)' and '$(filedate)' <= '$(finend)' then

// wanneer datum groter is as eerste dag van fin jaar maar kleiner as laaste dag van fin jaar en groter as 1819 fin jaar

concatenate('CurrentTemp')

LOAD*