Loading Limited files from a folder

[yuvraj_33]

Dear all,

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.

e.g.:

Test_29122016

Test_30122016

Test_31122016

Test_01012017

Test_02012017..... etc.

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.

[maxgro · Accepted 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));

DO

       // 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;

[tamilarasu]

Hi Yuvaraj,

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;

[yuvraj_33]

Thnx a lot maxgro, nagaraj... Both solutions are working fine..

[maxgro]

Qlik Community Tip: Marking Replies as Correct or Helpful