Replacing 0 values with null

Hi @AminArbab, welcome to the KNIME community.

Following on from @HansS 's suggestion, in the rule engine you could also use the NOT syntax, to do the same thing.
e.g.

NOT $Random Values$ = 0 => $Random Values$

Multiple Columns?

Setting a specific value (often 0) to Missing across multiple columns is something that comes up on the forum from time to time and it’s perhaps surprising that there isn’t yet an opposite to the Missing Value node. The Missing Value node, by the way, is designed to turn missing values into another value rather than the other way round.

There are a number of ways to achieve this across multiple columns but I’m not aware of any out-of-the-box “perfect” solution that covers all situations. Ideally there would be a multi column rule engine, to do as per @HansS’s suggestion, but unfortunately there isn’t.

Math Formula (Multi-Column) could achieve it if all your columns to be modified are integer columns (and you tick the “Convert Select Columns to Int” box to stop it (annoyingly :wink: ) converting the result to Double!

if($$CURRENT_COLUMN$$==0,"",$$CURRENT_COLUMN$$)

image

If the zeroes are in String Columns, then Math Formula won’t work, so an option would be String Manipulation (Multi Column) with this formula:

string(
$$CURRENTCOLUMN$$.equals(“0”)?toNull(“”):$$CURRENTCOLUMN$$
)

but if they are in a mix of string and integer columns, this will have the undesirable effect of converting everything to string!

I have been toying with a component to make it easy to replace a Value (or Values, using regex) with Missing Value across multiple columns and yet still respect datatypes.

The component below should enable that. It uses a different method to those described above. It unpivots the data so that the data to be transformed sits in a single column and then after transformation pivots it back. It then attempts to enforce the original data types for the columns.

It’s a newly created component, so if you do try it, please give any feedback or let me know of any problems encountered:

3 Likes