I am attempting to use the Math Formula (Multi Column) node to calculate the cube root of some data containing both positive and negative values. I am using the formula:
($$CURRENT_COLUMN$$)^(1/3)
However, negative values are turned to ‘?’. Does anyone have a suggestion on how to fix this?
It looks like this behavior is due to how the underlying library, the Java Math Expression Parser (or JEP) works. A similar was issue was brought up to the JEP developers back in 2006 (See thread here.)
An admittedly clunky workaround might be to use the Column Expressions node instead of the Math Formula (Multi Column) node. You could implement some logic where if the value you are testing is negative, instead return
-abs($$CURRENT_COLUMN$$)^(1/3)
I haven’t tried this out myself yet - it’s just off the top of my head - so I may be missing some nuance here.
The outputName flow variable was set to currentColumnName from the Column List Loop Start.
This seemed to work alright, albeit abit slow. The limiting factor seemed to be the Loop End (Column Append) node, which slowed down over the ~780 columns (~110,000 rows). Is there any faster/better way to this?
Hi Esben,
I’m not Scott but maybe this will help a little:
With a little prodding we can get the math formula node to give the correct value still which will be a bit faster than the the column expression node. Also we can use the Math Formula (Multi Column) node to get out of using the column loop.
Here’s the formula I used, the idea is similar to Scott’s in that we first take the absolute value, but then we dynamically multiply by 1 or -1 depending on if the original value was greater than or less than 0.