Replace Strings that represent null in complete table

[cdruf_iu]

I have a table with multiple columns that are all of type String. The values actually represent numbers, e.g., “1”, “2”, and missing values are encoded as “NA”. I want to convert all those columns to numeric and the “NA” should obviously be mapped to actual nulls.

My current solution is not satisfactory. I cannot believe it is that complicated. I use a “String Replacer” replacing “NA” with nothing for every column, and then “String to Number”. I believe there must be a single node for such a standard task.

[ipazin]

Hello @cdruf_iu,

welcome to KNIME Community!

You can use following expression in String Manipulation (Multi Column) node:

toInt( toNull( replace( $$CURRENTCOLUMN$$, "NA" , "") ) )

Although not that complicated still not straightforward as I would like

Br,
Ivan

[gonhaddock]

Hi @cdruf_iu
If you’re looking for real missing values (red ‘?’), I would use a ‘Rule Engine’ node with just one rule:

NOT $mycolumn$ LIKE "NA" => $mycolumn$

Single column solution, I’m sure that you can upscale it
BR

[gonhaddock]

@cdruf_iu
With the ‘String to Number’ node, you can handle all columns at a time replacing NA with missing.

BR