How to load only the latest files into Qlik

[#1]

We have a directory where we are generating a couple of files on a regular basis.

\\path\...

    file_01232017a.xls    1/23/2017

    file_01232017b.xls    1/23/2017

    file_01142017a.xls    1/14/2017

    file_01142017b.xls    1/14/2017

    file_nnnnnnnna.xls    mm/dd/yyyy

    file_nnnnnnnnb.xls    mm/dd/yyyy

Since the initial load, we have been manually updating the path for these files:

Data1:

LOAD...

FROM [\\path\file_01232017a.xls] (biff, embedded labels, table is filea$);

Data2:

LOAD...

FROM [\\path\file_01232017b.xls] (biff, embedded labels, table is fileb$);

How can we update this script so that it automatically just pulls the latest date (1/23/2017) of these two files?

Thanks for your support!

Trista

[sunny_talwar]

Have you checked this out?

Load latest Excel file to Qlikview

[m_woolf · Accepted solution]

One problem with trying to load the latest files is that over time the number of files in the folder increases and every file must be read to get the filetime.

If these files are being placed into the folder by some kind of batch job, consider moving the existing files into a backup folder.

Then your load can be *.xls

[#4]

Thanks for your response Sunny!

That solution is based on the characters of the filename. I would like to take the date of the file (modified or created) and check if it is the latest date compared to the rest of the files in the directory, if that's possible.

[sunny_talwar]

Use FileTime() instead of FileName function to determine the time on similar files.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/FileFunctions/FileTime...

[#6]

Thanks m w....

Since, our loads are scheduled after the files are dumped (which is also scheduled), is it possible to sort the files by date (latest being the first) and then check if it matches the date of the load?

[m_woolf]

Try something like:

if num(month(Today(0)))<10 then

  let vFileDateMonth = '0' & num(month(Today(0)));

ELSE

  let vFileDateMonth = num(month(Today(0)));

end if

if Day(Today(0))<10 then

  let vFileDateDay = '0' & Day(Today(0));

ELSE

  let vFileDateDay = Day(Today(0));

end if

let vFileDateYear = year(Today(0));

Let vFileDate = vFileDateMonth & vFileDateDay & vFileDateYear;

Data1:

LOAD...

FROM [\\path\file_$(vFileDate)*.xls] (biff, embedded labels, table is filea$);

[#8]

Thanks for providing this approach! It's formulating the date string to then add to the filename to grab that day's file. Since, this may be more maintenance work, we may just go with your original suggestion...save the existing files in a backup folder to allow access to just the latest files.

Thanks again!