IF, OR, AND Statement Rule Engine

I’m trying to replicate a nested IF Excel formula in KNIME and i wanted to ask on how i can achieve a nested IF using a rule engine and by referring on the logic below?

1 Like

Use column expressions node instead
br

Hi Ccas,

You could also split the tables according to the values and do the manipulation on the subsets. Eventually, you can concatenate it back together.
Or column expressions node will do the job.

Cheers,
Stiefel

Just for the excitement of having yet another option ;-)… if you really want to, you can do nested if statements within String Manipulation too. It’s hidden away, but it’s there:

e.g.

string(

$column1$.equals("Y")
?"Some result"
:$column2$.equals("Y") && $column3$.equals("N") 
   ?"A different result"
   :$column2$.equals("Y")
      ?"Yet another result"
      :"Bored now!"

)

The syntax is

condition
? result if true
: result if false

and as you can see, you can nest the conditions. It’s not the nicest syntax, but I do use it periodically when it reduces the complexity of using other nodes to achieve the same result.

As it is using java syntax for the expressions, && represents “AND”.
“OR” would be represented by || which is a pair of “pipe” symbols.
e.g.
:$column2$.equals("Y") || $column3$.equals("N")

You can use just a single & or | symbol too, but && and || would be marginally more performant as they cause it to stop evaluating as soon as sufficient conditions have been evaluated that the result of the whole expression will be logically known (google short-circuit evaluation) :wink: .

image
image


image

Fun with String Manipulation - Nested conditions.knwf (7.0 KB)

10 Likes

@Ccas , going back to your actual question though, within the Rule Engine you don’t nest conditions as such, but for each row of data that is evaluated, the rules in the rule engine are evaluated from top to bottom and the first one that matches wins. In effect it is one long IF ELSEIF ELSEIF statement

In terms of giving an example referring to your condition, if you re-post your condition as text, then it would be much simpler to refer to. Posting it only as an image makes life quite difficult.

I’m not going to try to rewrite your condition but in general for the rule engine, if you have nested excel conditions of this form:

IF(column1="Yes", "First result", if(or(column2="X", column3="Y"),"Second Result","Third Result")

… you can structure your rule engine rules in the same sequence

$column1$="Yes" => "First result"
$column2$ = "X" OR $column3$ = "Y" => "Second Result"
TRUE => "Third Result"

You should be able to adapt your condition on a similar basis. If you are still struggling, post back with what you have tried and I’m sure we will be able to help you further. I hope that helps

5 Likes

Hi @takbb, thank you for your input. I ended up using string manipulation with nested conditions based on the sample you provided. I find it more suitable in my use case to use a string manipulation node than using a rule engine. :slightly_smiling_face:

Nice on @takbb , didn’t know the String Manipulation supported this!!! This changes so many things!!! Among other things, less usage of the Column Expressions now!! Muhahahaha

(sneakingly tagging @ArjenEX as he loves Column Expressions :smiley: )

And even if I found out that it supported this, I would have never thought of nesting!!! That’s awesome @takbb !!!

5 Likes

This topic went the right direction with two people replying column expression but then the OG @takbb stepped in with a next level solution :rofl:

4 Likes

Lol, @bruno29a and @ArjenEX … That made me smile today…

My mental flowchart for whether to use Column Expressions almost always goes something like this…

Can it be done with String Manipulation?

Yes → use String Manipulation

No → use Java Snippet :rofl:

3 Likes

@ArjenEX , @bruno29a - I’m going a little off topic now, but I had been meaning to add something to Knowledge Sharing about String Manipulation for a while, so on the back of the above, I’ve added this… where you can see how to use String Manipulation to give you:

The current date and time
A random number
A UUID

Who knew? :wink:

2 Likes

Hi guys,

If you rule rule engine, you can do something like it so…

for comparation type, use as formal text:

$field1$ = $field2$ => TRUE

If you need to bring more cases, just use AND, OR…

($field1$ = $field2$ AND $field3$ > $field4$) OR $field5$ = 123 OR $field6$ LIKE “ABCDE” => TRUE

As you can see above, you have others examples when you select a kind of operation.

When you select a category, you have itens with explanation on click

“Like” and “Matches” are very cool, because you can use expression to match your formula…

AND THE TOP OF THE CAKE… you have others “rules nodes” to complete your search, as a dictionary for example for each row… then, if you have some rules to test, you can use it too…

image

I hope helped you…

Seeya,

Denis

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