topic Re: How to load latest excel from the folder in QlikView

How to load latest excel from the folder

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below requirement.

I have multiple excels in the subfolder like below.

daily they will add one excel. From all these excels I have to load only latest excel means with max date excel need to load dynamically.

For example in the above all excels "Extract20180616" is the latest excel this one need to load if any other excel Extract20180617 is added then it will be the latest so Extract20180617 has to be load.

Please help me on this.

Thanks in advance

Re: How to load latest excel from the folder

avinashelite — Mon, 18 Jun 2018 10:48:09 GMT

check this

Re: Pick the latest excel file from folders

How to load only most recent file | Qlik Community

Re: How to load latest excel from the folder

mahitham — Mon, 18 Jun 2018 11:03:41 GMT

Hi Avinash,

Thanks for your reply.
Actually I don't have today file I need to pass max(date) file.
Please help me to pass max(date ) in variable.

LET vToday = date(today(), 'DDMMYYYY');

LOAD *
FROM $(vToday)MyFile.xls;

Re: How to load latest excel from the folder

Anil_Babu_Samineni — Mon, 18 Jun 2018 13:36:08 GMT

How about this?

Table:

Load FileName() as File_Name,

Date(Date#(Right(FileName(),8),'YYYYMMDD'),'DD/MM/YYYY') as DateField

From Extract*.xls;

Min_Max:

Load Max(DateField) as MaxDate,

Min(DateField) as MinDate

Resident Table;

LET vMaxDate = Peek('MaxDate',0,'Min_Max');

Final:

Load * From Extract_$(vMaxDate).xls;

Re: How to load latest excel from the folder

mahitham — Mon, 18 Jun 2018 14:09:28 GMT

Hi Anil,

Thanks for your reply.

By making small changes its working fine.

Table:
Load FileName() as File_Name,
Date(mid(FileName(),8,8),'YYYYMMDD') as DateField,

// Date(Date#(mid(FileName(),8,8) ,'YYYYMMDD'),'YYYYMMDD') as DateField,
*
FROM [lib://Extract/Extract*.csv]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

Min_Max:
Load Max(DateField) as MaxDate,
Min(DateField) as MinDate
Resident Table;

LET vMaxDate = Peek('MaxDate',0,'Min_Max');

Drop table Table;
Final:
Load *
FROM [lib://Extract/Extract$(vMaxDate).csv]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);