MathFormula multi bug creating missing values

[Batjesen]

Hi,

In an attempt to change column data with integers and decimal data to missing values I attempted a suggestion with use of Math Formula multi columns as in: Replace specific row values of a column with NULL values - KNIME Analytics Platform - KNIME Community Forum

It worked “nicely” (relative expression when it could be easier to set values to missing) when I set just one column to if($a > -1000, 0/0, $a), the other columns are unaffected, but adding a similar if expression to another column makes all other columns missing values.

The real problem I have though is that it seems to be impossible to change values to be missing values in KNIME, and I know by searching the forum that I am not the only one having this problem - all “remedies” are perhaps just exploiting some quirks which are not easy to find. I would certainly have a well documented way or even a specific node for this purpose. - Right now it looks like writing a stored procedure in the database - bringing data back into KNIME and use missing value nodes and then back into the db table!

Regards, Batjesen
MathFmulti.knwf (80.7 KB)

[ArjenEX]

Hi @Batjesen

A few observations from my side:

@Batjesen

The real problem I have though is that it seems to be impossible to change values to be missing values in KNIME

I have to disagree on this. There are a variety of nodes out there that can do this. To name a few, depending on the data type: String Manipulation, Column Expression, Java Snippet, String Cleaner.

I’d also say the way you use the multi column node is somewhat unconventional. In the selected column section, you have all columns while you only have formula’s for A and B. The node therefore goes through all those columns but does not encounter a formula for C, D and E. The node is mainly intended to be used in such a way that you select the scope of the columns where the calculation should be applied to and thereafter call these with the $$CURRENT_COLUMN$$ variable.

Your formula mentions > -1000. All of the column match this rule so it is expected that all columns eventually will become null.