Discussion board where members can learn more about Qlik Sense Data Connectivity.
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.
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 MinDateResident Table;
Drop table Table;
//this part iterates over the file names in the folder and picks the latest date
FROM [lib://KPI 4/Table_$(vMaxDate)_*.xlsx](ooxml, embedded labels, table is Table)where not WildMatch([Account Book], 'US*');