Column Expressions Node - If statement to search for the existence of a substring

I would like to create a column called REVISION, the values in this column are based upon an IF…THEN…ELSE statement. The IF statement looks at the value in another field record called NAME. The IF statement will search NAME for the existence of a particular string and if that string exists, use the value given in the THEN part of the IF statement, if it is not found, use the value given in the ELSE part of the statement. I intend to create a complex expression which will contain about 100 of these IF THEN ELSE statement within one complex expression. The goal is to create a column called REVISION which will hold all the standardized names in it.

The typical IF statement I would use would be

IF(Instr(“Billy”,NAME)>0,“William”,
IF(Instr(“Ted”,NAME)>0,“Edward”,

ELSE NAME
))

It searches the search string (i.e. NAME field) to determine if the text exist and if so, what the position number is in the text. Anything over 1 means the formula found the text and it will go ahead and use the text in the THEN part of the IF statement, ELSE it will use the original value in the NAME field.

Any ideas on how you would do this in the COLUMN EXPRESSIONS NODE? Or if I should be using a different node all together?

Any help is appreciated.

Thank you.

For this case you’d better use Rule Engine
$NAME$ LIKE “Billy” => “William”

TRUE => $NAME$

5 Likes

Thank you izaychik63 for your solution-- it worked!

1 Like

Hi there @harveylim,

Welcome to KNIME Community!

Maybe wanna use Rule Engine (Dictionary) node in case there are 100+ cases…

Br,
Ivan

1 Like