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!

[jdunkerley79]

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]

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]

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]

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]

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]

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

James

[jdunkerley79]

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]

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]

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]

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])

 

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