topic Re: retrieving only last 3 weeks data while extract in QlikView

retrieving only last 3 weeks data while extract

manoranjan_d — Fri, 04 May 2018 08:17:03 GMT

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

Re: retrieving only last 3 weeks data while extract

jonathandienst — Fri, 04 May 2018 08:40:07 GMT

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.

Re: retrieving only last 3 weeks data while extract

sergio0592 — Fri, 04 May 2018 09:01:35 GMT

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;

Re: retrieving only last 3 weeks data while extract

manoranjan_d — Fri, 04 May 2018 09:27:17 GMT

Hi Jon,

my file name format is

name1_name2_2018-04-28.log

so how to retrive the date in the varibale of vDate

Re: retrieving only last 3 weeks data while extract

sergio0592 — Fri, 04 May 2018 09:35:06 GMT

maybe try with:

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