topic Re: How to load only the latest files into Qlik in QlikView

How to load only the latest files into Qlik

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to load only the latest files into Qlik

sunny_talwar — Tue, 24 Jan 2017 19:50:02 GMT

Have you checked this out?

Load latest Excel file to Qlikview

Re: How to load only the latest files into Qlik

m_woolf — Tue, 24 Jan 2017 20:06:18 GMT

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

Re: How to load only the latest files into Qlik

Tue, 24 Jan 2017 20:20:28 GMT

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.

Re: How to load only the latest files into Qlik

sunny_talwar — Tue, 24 Jan 2017 20:24:46 GMT

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.htm

Re: How to load only the latest files into Qlik

Tue, 24 Jan 2017 20:33:49 GMT

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?

Re: How to load only the latest files into Qlik

m_woolf — Tue, 24 Jan 2017 21:09:30 GMT

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$);

Re: How to load only the latest files into Qlik

Tue, 24 Jan 2017 21:32:59 GMT

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!