Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
yuvraj_33
New Contributor III

Loading Limited files from a folder

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Loading Limited files from a folder

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;

View solution in original post

4 Replies
MVP
MVP

Re: Loading Limited files from a folder

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;

View solution in original post

Re: Loading Limited files from a folder

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
New Contributor III

Re: Loading Limited files from a folder

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

MVP
MVP

Re: Loading Limited files from a folder