Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Function similar to "Coalesce"

kmcdowell
7 - Meteor

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!

21 REPLIES 21
JohnJPS
15 - Aurora

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
20 - Arcturus
20 - Arcturus

@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

 

Capture.PNG

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
kmcdowell
7 - Meteor

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
7 - Meteor

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
ACE Emeritus
ACE Emeritus

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:

2016-04-13_23-55-47.jpg

kmcdowell
7 - Meteor

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

jdunkerley79
ACE Emeritus
ACE Emeritus

Updated to version 1.1 now.

 

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

AlterEgo
5 - Atom

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
15 - Aurora

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.

Labels