Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (4)
1 Solution

Accepted Solutions
Highlighted
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
Highlighted
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.

Highlighted
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

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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.  

Highlighted
Creator III
Creator III

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)

Highlighted
Creator III
Creator III

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.