Function similar to "Coalesce"

Hello,

I am trying to find the best way to choose the first non-null variable for output from a list of variables. This is similar to the coalesce function in sql or SAS. So, if there are five fields, I would like to output the first one in the list that is not null. If all are null, then it would just output the last field. Any ideas would be appreciated. Thank you!

Preparation

Accepted answers

JohnJPS

The best I've been able to do is a set of nested IIF statements, e.g. something like:

IIF(!IsNull([Field1]),[Field1],
IIF(!IsNull([Field2]),[Field2],
IIF(!IsNull([Field3]),[Field3],[Field4])))

... which should be the same as:

coalesce(Field1,Field2,Field3,Field4)

Not as pretty but works.

MarqueeCrew

@kmcdowell,

If you've got lots of these fields to interrogate, I thought of a dynamic approach for you. It will give you the first non-null value from the input fields (selected in the TRANSPOSE Tool) and will give you NULL otherwise.

Thanks,

Mark

Coalesce.yxmd

jdunkerley79

As I like stealing ideas from the community for this kind of thing, and with @Ned's help, I implemented a formula add in that will give you coalesce as function inside the formula tool.

Need to be installed but you can download from https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/tag/v1.0

Extract and run Install.bat

Then you get:

Coalesce Test.yxmd

All comments