Any "CASE" or "COALESCE" equivalent?

Hi - I want to take a value and use a CASE or COALESCE (or IF ELSE END) statement to return different values depending on the source data (ie. compass degrees/45 -> N, NE, E, SE, S, SW, W, NW).

IF() only seems to handle true false, and would get ugly if I had to nest the IF’s for as many items as I needed to evaluate. Is there something like CASE/COALESCE and I am just missing it? Any other suggestions?


One way to do this is to define a dictionary object and use LOOKUP. E.g.

LOOKUP({"0": "N", "1":"NE", "2":"E"}, FLOOR(pageVars.compassHeading/45))

Of course filling in the rest of the key-value pairs.

If you need to reuse the same dictionary elsewhere, you can define an app variable and use the Initialize with a value advanced option to prefill your data (although it looks like that for this specific example you need to prefix the property names with e.g. deg_ since number-only keys are not allowed, but you can then just add "deg_" +) into the formula there).

1 Like

Ok! In my case, since the compass math results in integers, I just used an array index and that works fine.

(Altho it took me a few to figure out I had to PICK_ITEM to retrieve the array value and cannot do array accessor notation appVars.compassLabel[1] as that is supported in other contexts, just not here for some reason).

For more complex substitutions, I think the dictionary option would work better, so thanks for sharing that as well.

Alright, thanks for the feedback! We’ll see if we can ensure the [1] notation works more consistently. Can you share the exact formula that was failing?

Sure! I have an array-of-strings app variable on the global canvas:

compassLabel = ["N","NE","E","SE","S","SW","W","NW"]

And was attempting to resolve compass degrees to direction label in my formula using:


Which did not work. Instead I had to do:


…which works fine.