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

[jblomqvist]

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

or

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 · Accepted solution]

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]

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]

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

];

Escape sequences