Discussion Board for collaboration on QlikView Scripting.
I have a scenario where daily a file is generated as 'FileName_Date' in a folder.
FileName is common to all files but Date is daily date of the file in DDMMYYYY format.
At present there are files from last six months in folder that are created daily.
I have to fetch only those files that are two months previous to current month.
i.e. If today is 03-01-2017 then I have to load files from previous 2 months i.e from start of 01-11-2016 to 03-01-2017.
Kindly provide inputs on mentioned query.
Thanks in advance.
Go to Solution.
you can use a loop; replace the trace (bold line) with the load from your script
LET vDate = Floor(Today());
LET vFirstDate = Floor(addMonths(Today(), -2));
// file name
LET vFile = 'Test_' & Date(vDate, 'DDMMYYYY');
// load the file
TRACE LOAD .... FROM $(vFile);
// calc new date
LET vDate = vDate -1;
LOOP WHILE vDate >= vFirstDate;
View solution in original post
Change the highlighted text as per your requirment and try this,
LET vFilePath = 'Your Folder Path'; Data:Load '' as Temp AutoGenerate 0; FOR EACH File in FileList('$(vFilePath)\*.xlsx'); If Date(Date#(Right(Replace(SubField('$(File)','\',-1),'.xlsx',''),8),'DDMMYYYY'))>= Monthstart(AddMonths(Today(),-2)) then Concatenate(Data) LOAD *, FileName() as FileName FROM [$(File)] (ooxml, embedded labels, table is [Sheet1]); End If NEXT DROP Field Temp;
Thnx a lot maxgro, nagaraj... Both solutions are working fine..
Qlik Community Tip: Marking Replies as Correct or Helpful