Replace Strings that represent null in complete table

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.

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 :slight_smile:



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

1 Like

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



This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.