topic Loading Limited files from a folder in QlikView

Loading Limited files from a folder

yuvraj_33 — Tue, 03 Jan 2017 14:23:18 GMT

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.

Re: Loading Limited files from a folder

maxgro — Tue, 03 Jan 2017 14:47:18 GMT

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;

Re: Loading Limited files from a folder

tamilarasu — Tue, 03 Jan 2017 14:53:09 GMT

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;

Re: Loading Limited files from a folder

yuvraj_33 — Tue, 03 Jan 2017 15:23:42 GMT

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

Re: Loading Limited files from a folder

maxgro — Tue, 03 Jan 2017 15:25:50 GMT

Qlik Community Tip: Marking Replies as Correct or Helpful