topic Re: Match string values with single quote characters in QlikView

Match string values with single quote characters

Wed, 05 Aug 2015 07:04:42 GMT

I need to set a flag in my script where a field equals 1 of 3 specific strings. 2 of those strings have an apostrophe in them, and I cannot change them. (in fact 1 string uses chr(39) and the other uses chr(8217) )

So, I used a combination of Match and Purge to deal with the apostrophe characters and test if the field matches. This works for the 2 fields with the apostrophes, nut not for the 'No show' option. What am I missing?

if(match(PurgeChar(PurgeChar([Cancellation Reason],chr(39)),chr(8217)),'No Show','Rescheduled – Client didnt show up','Cancelled - Client didnt show up')>0,1) as fCancelReasonNoShow

or is there a simpler way to achieve this?

Re: Match string values with single quote characters

tamilarasu — Wed, 05 Aug 2015 07:09:10 GMT

Hi Derek,

Have you tried WildMatch!! Can you post here [Cancellation Reason] field values (sample)?

Re: Match string values with single quote characters

datanibbler — Wed, 05 Aug 2015 07:09:16 GMT

Hi Derek,

I'd guess you have to get rid of the blank space. It might be a possibility to REPLACE() this with an underscore so that you have the text all_in_one_piece? Otherwise, QlikView_functions might not work correctly because the second part of a string (after a blank space) is interpreted as a new parameter.

HTH

Re: Match string values with single quote characters

maxgro — Wed, 05 Aug 2015 07:15:57 GMT

try this

=match(trim(KeepChar(lower([Cancellation Reason]), 'no shw')), 'no show')

to test

=match(trim(KeepChar(lower('No ' & chr(39) & chr(8217) & 'Show'), 'no shw')), 'no show')

Re: Match string values with single quote characters

sasiparupudi1 — Wed, 05 Aug 2015 07:16:32 GMT

I suggest you to load PurgeChar(PurgeChar([Cancellation Reason],chr(39)),chr(8217)) as a seperate field in the table to see if how your resultant string would look like.

Could you post some sample data?

HTH

Sasi

Re: Match string values with single quote characters

tamilarasu — Wed, 05 Aug 2015 07:17:15 GMT

Derek,

My first correct answer in this community. to you.

Re: Match string values with single quote characters

Wed, 05 Aug 2015 07:21:49 GMT

thanks for all the quick responses - Wildmatch fixes the issue.

For reference, the data I was trying to match in the [Cancellation Reason] field is

No Show (no special characters)

Rescheduled – Client didn't show up (apostrophe = chr(39))

Cancelled - Client didn't show up (apostrophe = chr(8217))