Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ibradly
Contributor III
Contributor III

How to Load Only Latest Excel File

I want to load the latest excel file into my script from a specific folder. 

The filename is "DAC_GROUP_2020-03-07T1100"

I am not able to finish the script for it to load the latest file.

This is how the script looks like right now:

Table_Temp:

Load FileName() as File_Name,

(right(FileName(),20)) as Test

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

FROM [lib://Dropbox (dm1_qsuser)/AllClients\Simplifi\DAC_Group_*.xlsx]
(ooxml, embedded labels, table is sheet1);

 

Table_Temp2:
Load

Date(Date#(Left(Test,10),'YYYY-MM-DD'),'DD/MM/YYY') as Date

Resident Table_Temp;

drop Table Table_Temp;

 


Min_Max:

Load Max(Date) as MaxDate,

Min(Date) as MinDate

Resident Table_Temp2;

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

Final:

Load * FROM [lib://Dropbox (dm1_qsuser)/AllClients\Simplifi\DAC_Group_$(vMaxDate).xlsx]
(ooxml, embedded labels, table is sheet1);

 

Can someone please help?

Labels (4)
1 Solution

Accepted Solutions
JustinDallas
Specialist III
Specialist III

If you look at the error, you see that it's looking for a file named

 

DAC_GROUP_43905...

 

Whereas your file is named

 

DAC_GROUP_2020-03-07T1100

 

You have to format that date from 43905 to the one that matches your filenames.

 

View solution in original post

5 Replies
JustinDallas
Specialist III
Specialist III

Is it giving you an error?  What's it doing that it's not supposed to do?

ibradly
Contributor III
Contributor III
Author

it is giving me the following error.

qlik error.png

 

ibradly
Contributor III
Contributor III
Author

It is suppose to load the latest file in the folder.

JustinDallas
Specialist III
Specialist III

If you look at the error, you see that it's looking for a file named

 

DAC_GROUP_43905...

 

Whereas your file is named

 

DAC_GROUP_2020-03-07T1100

 

You have to format that date from 43905 to the one that matches your filenames.

 

JustinDallas
Specialist III
Specialist III

You may also want to consider this post here.

Qlik Community: latest-file-of-a-folder