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*
FROM
[$(filecurrent)]
(fix, codepage is 1252);
end if
LET vLastReload = Right(SubField('$(filecurrent)', '.', 1), 6);
STORE CurrentTemp into "D:\DEV\Anri\TOETS\Takbuffer$(FinCurrent).qvd" (QVD);
next
