Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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