How to load latest excel from the folder

[mahitham]

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

[avinashelite]

check this

Re: Pick the latest excel file from folders

How to load only most recent file | Qlik Community

[mahitham]

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;

[Anil_Babu_Samineni · Accepted solution]

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;

[mahitham]

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