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