topic Help with filebasename() in QlikView

Help with filebasename()

Sun, 08 Apr 2012 12:36:22 GMT

Hi, im fairly new to Qlikview, and im having problems with filebasename, im trying to get a list of all my film files, i.e. all my avi and iso files in a directory. I can get the filepaths, but i can't whatever i try get the filebasename to work correctly, i keep running it, and modifying but i either get errors, or blanks:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
sub DoDir (Root)
for each Ext in 'avi', 'iso', 'qvs', 'qvt', 'qvd'
for each File in filelist (Root&'\*.' &Ext)
Load 
    '$(File)' as Name, 
    FileSize( '$(File)' ) as Size, 
    FileTime( '$(File)' ) as FileTime,
    Filebasename( '$(File)' ) as FileBase,
    autogenerate 1;
next File
next Ext
for each Dir in dirlist (Root&'\*' )
call DoDir (Dir)
next Dir
end sub
call DoDir ('D:')

Help with filebasename()

jonathandienst — Sun, 08 Apr 2012 14:07:02 GMT

Unfortunately filebasename() does not work the way you expected. It takes no parameters and returns the base name of the text file currently being read. So it only works when reading a text file.

To get the basename in the posted script, you need to use subfield, like this:

// to return file name + extension

Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1) as FileBase

// to return filename without extension

Subfield(Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1), '.', 1) as FileBase

A little more complex, I am afraid!

Hope that helps

Jonathan

Help with filebasename()

Sun, 08 Apr 2012 17:54:45 GMT

Thanks, this worked great, is there any way I could have seen this from the documentation?

Re: Help with filebasename()

stilldvs — Tue, 09 Jan 2018 20:12:03 GMT

Working my way through the Qlik Sense Cookbook in 2018. Your tip from 2012 helped me past a hurdle in Ch 3. Thanks!