Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Loading the latest excel file from a folder into Qlik Sense Desktop

I needed to load the latest file from a shared folder into Qlik Sense desktop, while filtering out certain data during the load process on a daily basis.

The tricky part was that the file would always have a time extension along with the date, i.e. filename_yyyymmdd_hhmmss.xlsx and is > 150mb each.

I've seen a number of very useful posts here in the community, however all of them suggested to iterate through each file, which given their size made it pretty much unusable. 

I hope my solution may be useful to anyone facing a similar problem.

Table:


Load FileName() as File_Name,

Date(Left(Right(FileName(), 20), 8), 'YYYYMMDD') as DateField

FROM [lib://KPI 4/Table*.xlsx];
//(ooxml, embedded labels, table is Table);

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

Let vMaxDate=PEEK('MaxDate',0,'Min_Max');

Drop table Table;

//this part iterates over the file names in the folder and picks the latest date

Table:
Load *

FROM [lib://KPI 4/Table_$(vMaxDate)_*.xlsx]
(ooxml, embedded labels, table is Table)
where not WildMatch([Account Book], 'US*');

0 Replies