Discussion board where members can learn more about Qlik Sense App Development and Usage.
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.
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.
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)
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.