topic [Simple problem] This IF statement with OR's won't evaluate because the search value has a single quotes. How to fix this? in QlikView

[Simple problem] This IF statement with OR's won't evaluate because the search value has a single quotes. How to fix this?

jblomqvist — Wed, 21 Oct 2015 15:38:50 GMT

Hello all,

I have the following IF statement in my script:

If(CategoryName = 'Women's Clothes' or CategoryName= 'Sportswear' or CategoryName = 'Men's Footwear', 1,0) as CategoryType

But the script won't run because Women's Clothes and Men's footwear actually contain single quotes too and Qlikview sees this as a problem.

I thought I could do:

If(CategoryName = "Women's Clothes" or CategoryName= 'Sportswear' or CategoryName = "Men's Footwear", 1,0) as CategoryType

If(CategoryName = [Women's Clothes] or CategoryName= 'Sportswear' or CategoryName = [Men's Footwear], 1,0) as CategoryType

But this does not work.

Does anyone know how to fix this minor issue in my IF statement please?

sunny_talwar — Wed, 21 Oct 2015 15:50:14 GMT

Try this may be:

If(Match(CategoryName, 'Women' & Chr(39) & 's Clothes', 'Sportswear', 'Men' & Chr(39) & 's Footwear'), 1, 0) as CategoryType

Script:

Table:

LOAD *,

If(Match(CategoryName, 'Women' & Chr(39) & 's Clothes', 'Sportswear', 'Men' & Chr(39) & 's Footwear'), 1, 0) as CategoryType;

LOAD * Inline [

CategoryName

Women's Clothes

Sportswear

Men's Footwear

ABC

DEF

GHI

];

jblomqvist — Wed, 21 Oct 2015 15:52:48 GMT

Hi Sunny,

You are a star! Thanks that works. I thought about using the Chr function but figured there might be a simpler way than that. Maybe not I guess.

swuehl — Wed, 21 Oct 2015 23:16:04 GMT

I think there is also the easier option using an escape sequence with two single quotes:

Table:

LOAD *,

If(Match(CategoryName, 'Women''s Clothes', 'Sportswear', 'Men''s Footwear'), 1, 0) as CategoryType;

LOAD * Inline [

CategoryName

Women's Clothes

Sportswear

Men's Footwear

ABC

DEF

GHI

];