topic Re: Ignoring apostrophes in folder name in QlikView

Ignoring apostrophes in folder name

jjohnstonti618 — Tue, 09 May 2017 14:51:09 GMT

I am familiar with keepchar, purgechar, and a few other methods utilized to deal with special characters with normal fields; however, my documentpath and file name have an apostrophe in them and are loaded using variables.

//Client name in this case is Bob's Apples and the code knows to automatically look in a preceding folder to find the client name.

/*Code to define your client name based on folder structure--works fine

Let vClientName = SubField(DocumentPath(),'\',substringcount(DocumentPath(),'\'));

// 2 files loaded from 2 different folders depending on which step of the process were in (1 or 2)...these also load fine for Bob's Apples

Let vStepOneFile = chr(39) & 'T:\clients\$(vClientName)\1-Actuals\Actuals Final.xlsx' & chr(39);

Let vStepTwoFile = chr(39) & 'T:\clients\$(vClientName)\2-Benchmark Rates\Benchmark Rates Final.xlsx' & chr(39);

//These fail when my client name has an apostrophe, such as the case with Bob's Apples. It accounts for the apostrophe when i set the client name variable, as well as when i load the files from a folder name WITH an apostrophe, but thats it.

Let vStepOneFileExists=if(FileSize($(vStepOneFile))>0,-1,0);

Let vStepTwoFileExists=if(FileSize($(vStepTwoFile))>0,-1,0);

The error i get reads:

Syntax error

Unexpected token: 's', expected nothing

Let vStepOneFileExists=if(FileSize('T:\clients\Bob'>>>>>>s<<<<<< Apples\Actuals\Actuals Final.xlsx')>0,-1,0);

Any ideas?

Re: Ignoring apostrophes in folder name

stigchel — Tue, 09 May 2017 15:02:40 GMT

You can probably use an escape character e.g. 'Bob''s Aples', see also

https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/08/escape-sequences

Re: Ignoring apostrophes in folder name

sunny_talwar — Tue, 09 May 2017 15:05:08 GMT

May be try this

Let vClientName = Replace(SubField(DocumentPath(),'\',substringcount(DocumentPath(),'\')), Chr(39), Chr(39)&Chr(39));

Trying to see if Escape sequences works here

Re: Ignoring apostrophes in folder name

jjohnstonti618 — Tue, 09 May 2017 15:07:22 GMT

Thank you both for quick replies I will try both of these and report back

Re: Ignoring apostrophes in folder name

sunny_talwar — Tue, 09 May 2017 15:09:51 GMT

The idea behind my suggestion is same as stigchel‌‌'s, I guess I am just proposing the way to execute his and mine suggestion in your application

Re: Ignoring apostrophes in folder name

jjohnstonti618 — Tue, 09 May 2017 15:17:12 GMT

So that definitely worked to get by the first part..thank you!!!

But later in the script when i actually load the data from the excel file, its looking for the double quote now:

2017-05-09 11:10:17 0433 FROM

2017-05-09 11:10:17 0434

2017-05-09 11:10:17 0435 (ooxml, embedded labels, table is [Sheet1])

2017-05-09 11:10:17 Error: Cannot open file: 'T:\clients\Bob''s Apples\Actuals\Shipments Final.xlsx'

2017-05-09 11:10:48 Execution Failed

2017-05-09 11:10:48 Execution finished.

Re: Ignoring apostrophes in folder name

sunny_talwar — Tue, 09 May 2017 15:19:31 GMT

Add single quotes while loading also

FROM ['$(vClientName)']

Re: Ignoring apostrophes in folder name

jjohnstonti618 — Tue, 09 May 2017 15:31:26 GMT

i tried doing that 3 different ways and it still is looking for the double quote in the client name... its ok i will troubleshoot this a little further...thanks for the help

Re: Ignoring apostrophes in folder name

sunny_talwar — Tue, 09 May 2017 15:42:50 GMT

I guess you can create two variables or change the variable back to a single quote?

Let vClientName = Replace(SubField(DocumentPath(),'\',substringcount(DocumentPath(),'\')), Chr(39), Chr(39)&Chr(39));

Do stuff

Let vClientName = Replace(SubField(DocumentPath(),'\',substringcount(DocumentPath(),'\')), Chr(39)&Chr(39), Chr(39));

FROM ....;

Re: Ignoring apostrophes in folder name

jjohnstonti618 — Tue, 09 May 2017 15:48:04 GMT

That worked... you rock! Thank you

Re: Ignoring apostrophes in folder name

rwunderlich — Tue, 09 May 2017 16:22:51 GMT

Nice solution as always from Sunny. Note that you can get the last folder in the path by using a negative index in substring:

SubField(DocumentPath(),'\',-2)

A little neater than the substringcount().

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Ignoring apostrophes in folder name

sunny_talwar — Wed, 10 May 2017 00:07:04 GMT

Thanks Rob!!! Looking forward to meeting with you at the Qonnections