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
jdunkerley79
ACE Emeritus
ACE Emeritus
Hi @AlterEgo,

Sorry didn't work for you.

Which version of Alteryx are you running and on which OS?

Happy to jump on a Google Hangout/Webex and try and sort out with you. DM me if you want.

AlterEgo
5 - Atom

Hi JD,

 

Im running Windows 10 x64 and Aleryx 10.5 (latest version).

 

The insall batch worked fine.  The files are in their correct location.  It appears the DLL just can't be loaded into Alteryx when referencing formulas contained with the DLL.  Other forumulas references within the XML work just fine.

 

Is it because the DLL failed to register?

AlterEgo
5 - Atom

Im running the latest version of Alteryx 10.5.  The files do exist within their referenced locations.

 

All functions referenced within the XML work just fine.  Only those that refer to the DLL seem to fail.

jdunkerley79
ACE Emeritus
ACE Emeritus

Odd cant think of any obvious reason.

 

Assume Alteryx is a 64-bit install?

 

If you have time for a webex or similar let me know will try a fix

kjm04d
5 - Atom

Hey JD,

 

I am getting the same error when using the Coalesce function.  I would be happy to talk about this if possible to get it resolve.

 

Kyle

jdunkerley79
ACE Emeritus
ACE Emeritus

Sure happy to see if we can fix. DM me and we can set up a webex.

 

James

jdunkerley79
ACE Emeritus
ACE Emeritus

With thanks to @Ned, there is a v1.1.1 now released on GitHub which fixed @kjm04d issue.

 

https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/tag/v1.1.1

 

 

lhk2000
7 - Meteor

Hi,

 

I am still facing the issue with my alteryx being unable to utilize the JDformulaAddin.dll .

Installation was good, but that particular add in wasnt installed before I moved it physically.

 

Can you please advise?

 

Lars

jdunkerley79
ACE Emeritus
ACE Emeritus

Hi Lars,

 

Sorry, you are having difficulties.

 

I would suggest you use the newest version of the addins: https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/latest

 

As before, download and extract the zip, then run Install.bat (or Install Win7.bat if on WIndows 7). It should copy everything needed to the FormulaAddIn folder.

 

If it still doesn't work happy to have a Google Hangout or similar with you - DM me if you want to organise

 

James

troyfurnace
7 - Meteor

I have a simplistic solution that is easy to specify/maintain (e.g., not forgetting parentheses) as long as the number of fields is a known quantity - just leveraging Alteryx's Formula tool's ability to reference and overwrite columns repeatedly.

 

In the example picture, say your target column is "Coalesced".

Set it at the beginning to the first column's value.

Then make as many copies as you need, with formula like this:

iif(IsNull([Coalesced]), [Field2], [Coalesced])

next:

iif(IsNull([Coalesced]), [Field3], [Coalesced])

etc.

And then you can do one more at the end for a default value if none found (e.g., "None").

iif(IsNull([Coalesced]), 'None', [Coalesced])

Coalesce formula.png

The second picture shows example data and results of the formulae.

Coalesce results.png

Labels