Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Creator III
Creator III

Multiple IF statements in a variable

I'm testing multiple fields to see which one has a value.  

If(not isnull(WOCrew), WOCrew,If(not isnull(TransferCrew),TransferCrew,If(not isnull(OutboundCrew),OutboundCrew,If(not isnull(Crew),Crew))))

Is there a better way to do this?  I looked at Pick and Match, but I don't think you can use Match to test for Null.

 

Thanks!

Labels (3)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

You can try a tricky solution like:

 

=Pick(Match(-1, Not IsNull(WOCrew), Not IsNull(TransferCrew), Not IsNull(OutboundCrew), Not IsNull(Crew)), WOCrew, TransferCrew, OutboundCrew, Crew)

View solution in original post

6 Replies
vunguyenq89
Creator III
Creator III

If your fields are numeric, you should be able to use Alt() function https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Scripting/Conditiona.... Otherwise, If seems to be the only choice.

tresesco
MVP
MVP

You can try a tricky solution like:

 

=Pick(Match(-1, Not IsNull(WOCrew), Not IsNull(TransferCrew), Not IsNull(OutboundCrew), Not IsNull(Crew)), WOCrew, TransferCrew, OutboundCrew, Crew)

mikegrattan
Creator III
Creator III
Author

Thanks, but the values in the fields are not numeric.

mikegrattan
Creator III
Creator III
Author

The expression works when it's used directly in a dimension's expression editor, but it won't evaluate correctly when used as a formula for a variable.  

mikegrattan
Creator III
Creator III
Author

As I said earlier, the Pick Match solution you posted does work in the expression editor for a calculated dimension.  I have noticed, however, that it doesn't always evaluate correctly.  I'm assuming that the order in which the statement is evaluated is left to right, so the following should check to see if Crew is null, then WOCrew, then TransferCrew, and then OutboundCrew.  If Crew is not null, then Crew should be used, otherwise check to see if WOCrew is null, if it's not, then WOCrew should be used, etc.  Is that correct?

=Pick(Match(-1, Not IsNull(Crew), Not IsNull(WOCrew), Not IsNull(TransferCrew), Not IsNull(OutboundCrew)), Crew, WOCrew,TransferCrew, OutboundCrew)

mikegrattan
Creator III
Creator III
Author

After further testing, it seems that the Pick Match was pulling incorrect results due to one of the fields not having an actual Null value.  I updated the data load to ensure all fields are set to Null if the value is 0, -, or Len = 0 and now the expression is working correctly.