retrieving only last 3 weeks data while extract

[manoranjan_d]

hi all,

in my extract folder which have log files from 2016 to 2018

example in the folder i have file name as listed below

1a_2018-05-04.log

2b_2018-04-30.log

3c_2018-04-28.log

4d_2017-02-04.log

5e_2017-01-04.log

6f_2016-03-04.log

7g_2016-02-04.log

8d_2016-01-04.log

now when i want to load the data in the script i dont want to take all the files from folder i need only last 3 weeks files to be retrived in the script

example current date - 3 weeks

in script level how to achieve this

[jonathandienst]

Something like

Let vTestDate = Today() - 21;

For Each vFile in FileList('$(path)\*.log')

     Let vDate = Date#(Mid(SubField(vFile, '\', -1), 4, 10), 'yyyy-MM-dd');

     If vDate >= vTestDate Then

          LOAD .... <load statement here>

     End If

Next

Assumes same structure in all files and that you have a path variable with the file path to the log files.

[sergio0592]

Hi,

You can achieve this with:

LOG_TABLE:
LOAD *,
if(Date(Date#(SubField(SubField(Id_log,'_',2),'.',1),'YYYY-MM-DD'),'DD/MM/YYYY')>=(today()-21),1,0) as Flag
INLINE [

Id_log
1a_2018-05-04.log
2b_2018-04-30.log
3c_2018-04-28.log
4d_2017-02-04.log
5e_2017-01-04.log
6f_2016-03-04.log
7g_2016-02-04.log
8d_2016-01-04.log
]
;

NoConcatenate

FINAL_LOG_TABLE:
LOAD *
RESIDENT LOG_TABLE
where Flag=1;

drop table LOG_TABLE;

[manoranjan_d]

Hi Jon,

my  file name format is

name1_name2_2018-04-28.log

so  how to retrive the date in the varibale of vDate

[sergio0592]

maybe try with:

Let vDate = Date#(SubField(SubField(v_File,'_',3),'.',1),'yyyy-MM-dd');