Help with filebasename()

[#1]

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:')

[jonathandienst · Accepted solution]

Hi

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

[#3]

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

[stilldvs]

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