Multiple IF statements in a variable

[mikegrattan]

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!

[vunguyenq89]

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 · Accepted solution]

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]

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

[mikegrattan]

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]

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]

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.