Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Partner
Partner

Multiple IF statements with AND / OR

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.

1 Solution

Accepted Solutions
Highlighted
Contributor III

Re: Multiple IF statements with AND / OR

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.   

View solution in original post

6 Replies
Highlighted
Valued Contributor II

Re: Multiple IF statements with AND / OR

maye be

 

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

 

Regards,
Taoufiq ZARRA
Highlighted
Contributor III

Re: Multiple IF statements with AND / OR

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.   

View solution in original post

Highlighted
Valued Contributor II

Re: Multiple IF statements with AND / OR

thanks @andoryuu  for '

Regards,
Taoufiq ZARRA
Highlighted
Contributor III

Re: Multiple IF statements with AND / OR

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

Highlighted
Valued Contributor II

Re: Multiple IF statements with AND / OR

@andoryuu  🤣💰

Regards,
Taoufiq ZARRA
Highlighted
Partner
Partner

Re: Multiple IF statements with AND / OR

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