topic Re: How to get current filename in filelist loop in QlikView

How to get current filename in filelist loop

neil-devlin — Fri, 09 Jul 2021 08:52:36 GMT

Hi I'm trying to get the filename of the current file im on in my Filelist loop and store that as a variable, is this possible? Filename() just returns a NULL value. I also need to extract the Filetime from the file too to store it as a variable.

Example code:

SET SalesFactFiles = $(vSourceDataFilePath)qvd\aggregated\monthly\*2021_salesfact.qvd;

FOR EACH FILE IN FILELIST('$(SalesFactFiles)')

LET Weeklyname = FileName($(File));
LET SalesfactTime = FileTime($(File));

Re: How to get current filename in filelist loop

PiEye — Fri, 09 Jul 2021 09:02:58 GMT

Hi Neil

You're doing the right thing, just need to ensure correct capitalisation and wrap the dollar sign expansion in quotes:

FOR EACH FILE IN FILELIST('$(SalesFactFiles)')

LET Weeklyname = FileName('$(FILE)');
LET SalesfactTime = FileTime('$(FILE)');

Re: How to get current filename in filelist loop

PiEye — Fri, 09 Jul 2021 09:04:57 GMT

Just to elaborate - you were almost there with FileName() - except this is used within a LOAD statement to get the name of the currently loaded table.

I use it when I am doing a pull directly from a wildcarded filname:

LOAD

FileName() AS NameOfFile

FROM [../../Data/QVD/MyFile_2020*.QVD] (QVD);

Re: How to get current filename in filelist loop

avinashelite — Fri, 09 Jul 2021 09:14:07 GMT

You can't use Filename() function outside the load statement, first load it in to a field and then you could use it to assign to variable

Re: How to get current filename in filelist loop

neil-devlin — Fri, 09 Jul 2021 09:19:12 GMT

Thanks for this Pi, this worked brilliant for FileTime but still returns NULL for FileName unfortunately, assume this can only be used in a LOAD statement as you've said? Is there any function to get the name of the current file in this instance outside of using something like 'Right('$(FILE)', count)' ?

Neil

Re: How to get current filename in filelist loop

PiEye — Fri, 09 Jul 2021 10:17:08 GMT

@neil-devlin FILE will just be the name of the file - so no need to wrap it in a formula:

LET Weeklyname = '$(FILE)';

This will return it with the full path, so yes, start from here and use a string function to parse it (hint: use"subfield" with a '/' delimiter 😉 )

Re: How to get current filename in filelist loop

neil-devlin — Fri, 09 Jul 2021 11:40:55 GMT

Thanks for the help Pi! Have my script doing what I need it to now 😁. The subfield hint was very much appreciated!

Neil

Re: How to get current filename in filelist loop

PiEye — Fri, 09 Jul 2021 13:17:17 GMT

Great to hear!