Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator II

How to load latest excel from the folder

Hi Experts,

Can any one please help me on below requirement.

I have multiple excels in the subfolder like below.

extraction file.png

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

1 Solution

Accepted Solutions
Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

4 Replies
mahitham
Creator II
Creator II
Author

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

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mahitham
Creator II
Creator II
Author

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