Function similar to "Coalesce"

[kmcdowell]

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!

[JohnJPS · Accepted solution]

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

@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

[kmcdowell]

Thanks so much!  This works!  I was trying something using a conditional configuration, but it wasn't giving me the correct results.  Much appreciated!

[kmcdowell]

Thanks for sharing this solution.  I am always up for learning something new.  While the nested boolean solution ended up working, I am going to hang on to this in case I need it in a later project.  Thanks for your help!

[jdunkerley79 · Accepted solution]

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:

[kmcdowell]

This is great!  Thanks so much--I am downloading it now.  This will be a great addition to my functions!

[jdunkerley79]

Updated to version 1.1 now.

Can be downloaded at https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/tag/v1.1

[AlterEgo]

I just installed this but Alteryx cannot open the dll.  The following error message occurs:

Unable to load dll: C\Program FIles\Alteryx\bin\RuntimeData\FormulaAddIn\JDFormulaAddIn.dll

Any ideas?

[JohnJPS]

Hi @AlterEgo, I believe your version of Alteryx is just using a different location to look for this stuff: you could try copying the .dll and reated .xml file to the location shown in the error message.