Change Empty Row values with values from another column

#1

Hello,

Very new to Knime. I hope somebody can help me.
I have a file with column Parent Name and another column, Child Name. In some rows the parent name is empty (with the ?). in those cases I want to have the value of the parent name that is in the same row.
Otherwise, just keep the value of the Parent Name.
I was trying something like this in the Column Expressions Node:

if (isMissing(column(“Parent Name”))) {
column(“Parent Name”) == column(“Customer Name”)
}
column(“Parent Name”)

But it is not working. I receive no error in the formula, but it makes no changes in the Parent Column, empty cells are still empty.

Any ideas?

Thks!!

0 Likes

#2

Hi @danubio1971

Welcome to the forum. I would try the Rule Engine node.

2 Likes

#3

Hi danubio1971

KNIME is a great tool and worth learning - welcome to the forum :slight_smile:

I have to admit that I am not familiar with the column expressions node as it’s relatively new. But I think a Rule Engine node would be much easier anyway.

Based on the syntax you attempted for column expression node, I’d try the following in the rule engine node instead: -

MISSING $Parent Name$ => $Customer Name$
TRUE => $Parent Name$

You’ll probably want to replace your parent name column. If you need a screenshot, let me know.

2 Likes

#4

Hi there @danubio1971,

As pointed out by @HansS and @stratlytic I would go with Rule Engine here as well. To learn more about Column Expressions and figure out why your code isn’t you can check out this video from KNIME Introductory course: https://www.knime.com/node/20992

If you will have any questions feel free to ask :wink:

Br,
Ivan

0 Likes

#5

Thank you very much guys! It worked perfect.
Another quick one, what if I need to do this with Parent Name and Parent #?
Do I need another Rule Engine node?


Because I will either have to choose “Append Column” or “Replace Column”, but I need to work in two different columns.

0 Likes

#6

HI @danubio1971,

you can’t change two columns with Rule Engine node. So yes, you would need another Rule Engine for second column. Column Expressions can handle multiple expressions.

Br,
Ivan

0 Likes

#7

Ok, so that bring me back to my first question.
why the code it is not working?
Also I tried:
if (isMissing(column(“Parent Name”)))
column(“Customer Name”)

but does not work also.
Any ideas?

0 Likes

#8

Let me rephrase my question, I guess I am using all the columns in these examples.
How do I identify a cell value in a specific column?
something like cell(column(“Parent Name”)))?

0 Likes

#9

Thank you @ipazin but I need to do this several times, so I need to do it in the Column Expressions node.
I am just trying to figure it out there, but I can’t. The video is good but it is not helping in this particular case.

0 Likes

#10

Hey @danubio1971,

I’m a big fan of Column Expressions myself. Is this what you are trying to do?

You can click the plus sign in the Column Expressions config and apply the same logic during this process or just add a second Column Expressions node to handle the second group.

My understanding is that the logic applied to columns in the node do not get saved until it’s executed, so if you want to add logic to another column that relies on the result of the first statement it won’t work properly.

Capture


Capture3

2 Likes

#11

@TardisPilot You nail it!!! Thank you very much!!!

3 Likes

#12

HI there @danubio1971,

Column Expressions is based on JavaScript so you can check this page whenever you are in a doubt:
https://www.w3schools.com/js/

Br,
Ivan

0 Likes

#13

Again, thank you to all of you guys for helping me here.
I have another issue here, the formula with Parent # it is not working. Its the same formula as Parent Name, which is working, but the one for Parent # it is not.
I see that the missing values in Parent Name have a “?”, the ones in Parent # do not since loading the file.
Is this a possible cause of the IsMissing not working?

0 Likes

#14

Hi there @danubio1971,

in KNIME missing values are denoted as red question marks. Column Parent # is not missing but rather empty so isMissing() function is not working in this case. You can use following syntax in If clause instead of isMissing() function:

column(Parent #)==""

or toNull() function prior to isMissing() in your current syntax.

Br,
Ivan

1 Like

#15

Excellent, it is working now! Thank you @ipazin and you all!!
Also, I know some js, so good to hear that I can use it here.
Cheers!

2 Likes

#16

Glad you made it @danubio1971 :slight_smile:
Ivan

0 Likes