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

a_krakovskyi — Tue, 28 Dec 2021 20:22:49 GMT

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

topic Loading the latest excel file from a folder into Qlik Sense Desktop in Connectivity & Data Prep

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