topic Re: String handling of file Path in QlikView

String handling of file Path

Thu, 02 Jan 2014 08:47:14 GMT

Hi all,

This is part of my script:

for each FoundFile in filelist( Root & '\*.csv')

let vTemp= '$(FoundFile)';

let vTemp2= upper(left(filename('$(FoundFile)'),len(filename('$(FoundFile)'))-21));

The result is:

vTemp

"C:\decisioning\log\dcs\1\1\dcs_meta_data_20131126113434.csv"

vTemp2

<NULL>

I'm trying to isolate the file name without the date stamp, extension and folders and to show it in upper case letters.

where am I wrong?

Thanks,

Boris

Re: String handling of file Path

DavidFoster1 — Thu, 02 Jan 2014 08:58:31 GMT

I dont think you need the single quotes around your $(Foundfile) declarations.

Re: String handling of file Path

Thu, 02 Jan 2014 09:02:19 GMT

If I remove them then I don't get event a result for vTemp

Re: String handling of file Path

DavidFoster1 — Thu, 02 Jan 2014 09:10:35 GMT

Sorry I misread the double quotes in your question. The issue is the filename function. It only works when loading data from a file. You should just look at using normal string functions.

please try:

LET vTemp2 = UPPER(RIGHT('$(vTemp)'),20);

Re: String handling of file Path

jonathandienst — Thu, 02 Jan 2014 09:11:34 GMT

You don't need to use expansions in these expressions, and filename() only works in a LOAD statement. Rather use subfield(), like this:

for each vFoundFile in filelist( Root & '\*.csv')

Let vTemp1 = SubField(vFoundFile, '\', -1); // file name including extension

Let vTemp2 = SubField(SubField(vFoundFile, '\', -1), '.', 1); // file name excluding extension

...

HTH

Jonathan

Re: String handling of file Path

rbecher — Thu, 02 Jan 2014 09:27:06 GMT

Hi Boris,

filename() function works only in a LOAD statement (see help). To extract the filename I would suggest to use index():

let vFileName = mid('$(FoundFile)', index('$(FoundFile)', '\', -1) +1);

let vTemp2= upper(left('$(FoundFile)', len('$(vFileName)') -21));

- Ralf

Re: String handling of file Path

Thu, 02 Jan 2014 09:28:10 GMT

Ok, but now I get the file name without the extension but with the date stamp.

how can I remove the date stamp?

if I use right function then I get the time stamp.

if I use left function I get partial names because each file has different name.

how can I remove only the date stamp and to get finally this result for the current path:

C:\decisioning\log\dcs\1\1\dcs_meta_data_20131126113434.csv

Result: DCS_META_DATA

Re: String handling of file Path

Thu, 02 Jan 2014 09:30:00 GMT

Doesn't work.

get only c:\dec

Re: String handling of file Path

DavidFoster1 — Thu, 02 Jan 2014 09:31:26 GMT

If it is a constant length then use left/right/mid functions to select what you need. If it is variable length then use the subfield function.

Re: String handling of file Path

rbecher — Thu, 02 Jan 2014 09:34:18 GMT

Sorry, my mistake:

let vFileName = mid('$(FoundFile)', index('$(FoundFile)', '\', -1) +1);

let vTemp2= upper(left('$(vFileName)', len('$(vFileName)') -21));

- Ralf