Replacing 0 values with null

[AminArbab]

Hi guy I’m fairly new to Knime and I’m having difficulties with replacing the 0 values in the columns with null(missing data value).
I tried missing value node but I can’t figure out how to configure the node.
I also tried the java snippet node and again I couldn’t find any success.
Can anyone help me please

[HansS]

Hi @AminArbab

Welcome to KNIME Forum. You can use a Rule Engine node with:

to make all 0 values missing

gr. Hans

[takbb]

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 ) converting the result to Double!

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

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:

Value to Missing – takbb

[AminArbab]

Hey there, it actually worked and helped me. Thank you appreciate it.

[rfeigel]

Please mark @takbb’s solution solved so others can know it worked properly.

[Daniel_Weikert]

Alternatively a division by zero error could potentially have worked too
br

[takbb]

@Daniel_Weikert

Alternatively a division by zero error could potentially have worked too

Hi @Daniel_Weikert , yes
tbh, I was quite surprised to discover that Math Formula would even accept "" in the statement

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

given that it doesn’t handle strings at all, but I came across it elsewhere on the forum and felt that in some ways it made the intent clearer (i.e. blanking the value), but division by zero is definitely another option…

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

Talking of making the intent clearer… this would work too,

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

Unfortunately I have yet to find a way of having Math Formula detect a missing value. All attempts to do anything conditional with it within Math Formula seem to result in a missing value…

[AminArbab]

I just had one more question, this node in this forum, it is not in the knime app itself and since this forum won’t be available in a month, how can I add it to knime?

[Daniel_Weikert]

Why is this forum not available anymore?
If you like to add it go to the link with the node and drag and drop it into KNIME
br

[hotzm]

I know this is already marked as solved, but might I suggest the Value Lookup node? Maybe it is useful to you in other contexts as well.
You can actually configure it to retain input values in case no match is made. We just provide a single row “0, ” as the dictionary table. So all other values than 0 won’t find a match.

Forum-77942-ValueLookupReplaceWithMissing – hotzm

The solution posted by @takbb is actually a (very cool) component, which you can drag&drop into your workflow from Hub like any other node you find there. If you are worried about it vanishing (which I don’t think will be the case), you can always “unlink” the component after you dropped it into a workflow and export it to somewhere in your local workspace. But I don’t think that is necessary, since each “instance” in your workflow is basically a complete copy of the component and can always be unlinked and exported (or copy&pasted).

[takbb]

thanks @hotzm!

@AminArbab, I think perhaps what you are asking is how to find this component (or other components) again in the future when you cannot quickly find this forum post, and you’ve forgotten exactly what it was called, or where you got the component from…

Wouldn’t it be really nice if KNIME had a repository built into the UI where you could “store” components that you find useful, just like they were nodes… but sadly that isn’t a facility yet although I keep hoping with every release

I have some suggestions:

• Keep a bookmarks folder in your browser dedicated to useful components, and bookmark the page for the component for future reference. Then each time you want it you can drag and drop the component from the browser onto a workflow as @Daniel_Weikert suggests.

• Create a workflow in your local area in KNIME called something like “_Favourite Components” (put an underscore at the beginning so it gets sorted to the top of your folders). Then any component you find useful, you can drop it on that workflow and when you want to use it in future you copy and paste it from that workflow into a new workflow. One downside to this is that you may not remember where the original came from, such as if you want to reach out to the author for support, but you could always add an annotation with information on the workflow.

• After downloading a component you want to “keep”, upload it to your own private space on the KNIME hub. Then each time you want the component, you can find it within KNIME explorer / “space explorer” by navigating to the hub and your private space. The disadvantage of this though is that you won’t get automatic updates for the component when the original gets bug fixes or new functionality.

---

Personally, I probably prefer the browser shortcuts idea, as this means I can refer back to the original location when I need it again, and I’m always pulling the latest version (or if the component has multiple versions created by the author, I can choose a specific version if necessary).

---

btw, if there are particular forum pages that you may want to refer back to in future, there is a bookmarks option at the bottom of each post, if you first press the three dots:

You can find stored bookmarks by click on your profile icon on the forum and selecting the bookmarks button

hope that helps!

[AminArbab]

thanks my friend you really helped me a lot.