Multiple IF statements with AND / OR

[thanhng34]

Hi everyone,

I’m a beginner in Qlik Sense. I want to add a new flag (Flag01) whose objective is to detect errors regarding the coherence between two existing fields (Gender and Title).

If Gender = ‘Male’ AND ‘Title’ is different of ‘Mr.’  -->  Error

If Gender = ‘Female’ AND ‘Title’ is different of ‘Mrs.’ Or ‘Ms.’ --> Error

I wrote an expression like below but it didn’t work :

If (Gender = ‘Male’ and ‘Title’ <> ‘Mr.’, ‘Error’,

If (Gender = ‘Female’ and wildmatch(‘Title’, ‘Mrs.’, ‘Ms.’) <1, ‘Error’,

‘OK’)) as Flag01

Can anyone help me ? I’m struggling to find the correct solution for a few days.

Thanks in advance for your help.

[Taoufiq_Zarra]

maye be

If(Gender ='Male' and Title <> 'Mr.', 'Error', If(Gender ='Female' and wildmatch(Title, 'Mrs.', 'Ms.') <1, 'Error', 'OK')) as Flag01

[andoryuu · Accepted solution]

I wouldn't use wildmatch unless I were using wildcard characters to check like '*Mrs.*'.  If you're just looking for a match you can use @Taoufiq_Zarra 's code with just "Match" (also, add a single quote before the final "Error").  I've modified it a bit to force it to be evaluated as a boolean instead of integer evaluation. 

If(Gender ='Male' and not Match(Title,'Mr.'), 'Error', If(Gender ='Female' and not match(Title, 'Mrs.', 'Ms.'), 'Error', 'OK')) as Flag01

Please note that in your original, you had single quotes around Title.  This will cause the function to evaluate it as a string literal.   

[Taoufiq_Zarra]

thanks @andoryuu  for '

[andoryuu]

np @Taoufiq_Zarra they're only 25 cents - you can venmo it to me 😄

[Taoufiq_Zarra]

@andoryuu  🤣💰

[thanhng34]

Thank you so much @andoryuu and @Taoufiq_Zarra  for your help. The expression with "not match" works really well in my case.