Can you help me with the following:
I have a table which is the result of a pivot populated by integer values and ‘missing’ values indicated by a red “?”. I want to replace the missing values with 0 (zero) however my efforts only replace the existing data, not the missing values.
The number of columns and column names changes dynamically depending on previous input into the pivot. The column names of interest are always prefixed by “I_” to facilitate filter selection.
I have tried Column Expressions, Missing value, Rule Engine, Math formula (MultiColumn), Group Start/End nodes with various coding ( eg
MathFormulaNode: if($$currentcolumn$$ == “?”, 0, $$currentcolumn$$)) to no avail. The Missing value node accomplishes the task of replacement perfectly but I don’t know of a way to allow for dynamically changing columns. The Math formula (MultiColumn) node does handles the changing columns as I can filter on the prefix (I_*) but the code I create does not replace the missing values.
I have searched related topics and implemented ChtGPT and BARD solutions but what i expect to work does not. Attached are screen shots and workflow.
Yes this does feel like a failing in the Math Formula node. The last time I checked it didn’t handle missing values, not even using “isNaN()” (Not-a-Number) which you’d think it would, and as far as I can see it still doesn’t, which is frustrating.
Options? OK, so if you are wanting to replace missing values in all of your Integer and/or Double columns, you can use Missing Value node, and don’t go to the individual columns but instead enter defaults on the “default” tab:
No need to do anything for the individual columns.
If there are some numeric columns that you don’t want to replace missing values on though, that could present a problem though. You’d probably need to use a Column Splitter first so Missing Values would act only on the required columns and then Column Appender to bring everything back together.
Alternative… oddly String Manipulation (Multi-Column) can handle it to an extent, but you would need to specify the columns (which doesn’t help with your dynamic columns problem), and you’d need to do Integers and Doubles as separate calls, so probably wouldn’t be as convenient for your use case. It can be done “semi-automatically” by utilising some additional prior nodes to select the required column lists into flow variables, but that would require some extra attention.
This is probably about the third time in as many weeks that I’ve commented on String Manipulation (multi-column)'s inability to allow us to select columns by data type. Maybe there is a need for it!
It would be nice if Math Formula node could be improved though.
Does any of the above get you where you need to go?
Thank you so much. I implemented the Missing value option and employed the Column splitter using the prefix (I_*) to filter the columns then Column appender to stitch it back.
Agree that it is frustrating that the Math formula node does not handle the missing values as expected since the functionality appears to be there.
