topic Re: trim file path in QlikView

trim file path

Mon, 20 Oct 2014 21:14:20 GMT

i have field like below

c:/rootfolder/file1.txt

c:/rootfolder/file2.txt

how can i trim the path and only get file1.txt file2.txt

Re: trim file path

maxgro — Mon, 20 Oct 2014 21:20:20 GMT

=subfield('c:/rootfolder/file1.txt', '/', 3)

or filename, from online help

Returns a string containing the name of the table file currently being read, without path but including the extension.

Example:

Load *, filename( ) as X from

C:\UserFiles\abc.txt

Will return 'abc.txt' in field X in each record read.

Re: trim file path

albertovarela — Mon, 20 Oct 2014 21:22:07 GMT

Hello, use the subfield function. subfield('c:/rootfolder/file2.txt','/',-1) . BTW you might want to check your example.. it has forward slashes when they're supposed to be '\'...

hope this helps

Re: trim file path

Mon, 20 Oct 2014 21:29:59 GMT

how can i direct output of this code into qvd file.

set vRoot = 'C:\vbscript';

FOR Each Ext in 'qvd'

FOR Each File in filelist ('$(vRoot)'&'\*.'&Ext)

Load subfield('$(File)','\',3) as Name, FileSize( '$(File)' ) as

Size, FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

next Ext

Re: trim file path

Tue, 21 Oct 2014 09:02:57 GMT

this will work

=subfield('c:/rootfolder/file1.txt', '/', 3)

Re: trim file path

albertovarela — Tue, 21 Oct 2014 15:59:44 GMT

Give your table a name then use the STORE statement: e.g.

set vRoot = 'C:\vbscript';

FOR Each Ext in 'qvd'

FOR Each File in filelist ('$(vRoot)'&'\*.'&Ext)

TableList:

Load

subfield('$(File)','\',-1) as Name, //

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

next Ext

STORE TableList into C:\path\TableList.qvd;

Re: trim file path

Mon, 27 Oct 2014 20:56:26 GMT

I get syntax error and execution error when i do this:

set vRoot = 'C:\vbscript';

TableList:

FOR Each Ext in 'qvd'

FOR Each File in filelist ('$(vRoot)'&'\*.'&Ext)

TableList:

Load

subfield('$(File)','\',-1) as Name, //

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

next Ext

STORE TableList into C:\path\TableList.qvd;

Re: trim file path

albertovarela — Mon, 27 Oct 2014 21:01:20 GMT

Are you using a valid path?

Re: trim file path

Mon, 27 Oct 2014 21:09:35 GMT

yes

Re: trim file path

albertovarela — Mon, 27 Oct 2014 21:23:04 GMT

Delete the 3rd line on the script (where says TableList:). That will fix it. You are specifying the table name right before the LOAD statement

Re: trim file path

Mon, 27 Oct 2014 21:29:22 GMT

then it will complain table name Tablelist not found. without declaring table how can you store.

Re: trim file path

albertovarela — Mon, 27 Oct 2014 21:36:48 GMT