Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
thanhng34
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.

Labels (3)
1 Solution

Accepted Solutions
andoryuu
Creator III
Creator III

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
Taoufiq_Zarra
Master II
Master II

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

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
andoryuu
Creator III
Creator III

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

Taoufiq_Zarra
Master II
Master II

thanks @andoryuu  for '

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
andoryuu
Creator III
Creator III

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

Taoufiq_Zarra
Master II
Master II

@andoryuu  🤣💰

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
thanhng34
Partner
Partner
Author

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