Combining different rules engine nodes

Hello,

I have defined n different rule engine nodes (combined with string manipulation) where I check always on other columns:
e.g.
Rule Engine node1:
MISSING $COLUMN1$ =>“NO COLUMN1”
TRUE=>COLUMN1A

Rule Engine node2:
MISSING $COLUMN2$ =>“NO COLUMN2”
TRUE=>COLUMN2A

So at the final I generate n New columns. It works fine, but here, I have always to connect from 1 engine node (String Manipulation) to the other engine node. Isn’t there a more efficient way of doing this?

Thanks for your answer.

Hi @keizersoz2 and welcome to the Knime Community.

You can use the Missing Value node to do this in 1 node.

Here’s a sample workflow I put together:
image

Input:
image

Results:
image

And this is how the Missing Value node is configured:

Here’s the workflow: replace missing values in multi columns.knwf (7.4 KB)

2 Likes

Hi @Bruno29a

Thank you very much for your reply!

It’s indeed very interesting to use the node. However in my case, I want to create n new columns with the results as I defined in the rule engine node. Is it possible with the missing value node or any other node?

Thanks for your reply!

Regards,
Keizersoz

Hello @keizersoz2,

don’t understand the TRUE clause. If not missing then you don’t change the value or you take value from another column (COLUMNxA)?

And btw welcome to Community!

Br,
Ivan

2 Likes

Hi @ipazin ,

Thanks a lot for you reply!

–>I have merged 5 different tables: table1,…,table5 to 1 new TABLE
–>After merging the tables, I want to create for 3 out of the 5 tables a new column based on some existing columns + 1 overview column

Actually it’s not about replacing missing values it’s rather that I want to create the new columns on the following way:

–pseudo code
if table1.FK is not null then
newcolumn1= concat(table1.column1;table1.column2)

if table2.FK is not null then
newcolumn2= concat(table2.column1;table2.column2)

if table3.FK is not null then
newcolumn3= concat(table3.column1;table3.column2)

if table1.FK is not null then or table2.FK is not null or if table3.FK is not null then
newcolumn4= 1

For the moment I do this combining different rule engine node and string mainpulation, but I was wondering if there was not a compacter way using less nodes.

Thanks for your answer.

Hello @keizersoz2,

seems Column Expressions node is then a way to go. You can output multiple columns and perform calculations/manipulations. It’s syntax is based on JavaScript. Take a look at this example:

Br,
Ivan

1 Like

Hi @keizersoz2 , in the case you can use Column Expression. You’d still need to define the rules for each of the columns you want to create, but they can all be done in this same node.

For example:

This will create 3 new columns:

Here’s the workflow: create new columns based on other columns.knwf (7.2 KB)

2 Likes

Hi @keizersoz2 , I’m not sure that my attached workflow satisfies the need to “use fewer nodes” or make things “simpler” :wink: , but it does provide a means of scaling the solution if you had more than just a few columns.

It assumes that your columns follow a standard naming convention so that an expression to handle each set of columns can be defined dynamically.

In my example, the sets of columns have the name:
Tn.FK
Tn.Col1
Tn.Col2

Using a Counting loop, set to iterate 3 times, either a Column Expression or a String Manipulation can contain elements of dynamic code.

The first example in the workflow is the more complex as it uses String Manipulation, and to achieve dynamic column references in that node, you need to define a flow variable containing the expression string. (I have yet to find a way of dynamically referencing a column directly in String Manipulation without doing this, but maybe somebody else will know a way if it exists)

The aim here is to create an expression of the form:
$T1.FK$==null?null:$T1.Col1$+$T1.Col2$
with T1 being replaced on each iteration (e.g. T1, T2, T3)

This expression means "if T1.FK is missing, then output null (missing) otherwise output the concatenation of T1.Col1 and T1.Col2. (This assumes that these columns are strings rather than numerics!)

As I said, to make that expression dynamic, it has to be supplied by a flow variable that is calculated in an earlier String Manipulation (variable) node for each iteration:

string("string($T"+($${IcurrentIteration}$$+1)+".FK$!=null?"+
"$T"+($${IcurrentIteration}$$+1)+".Col1$"+
"+$T"+($${IcurrentIteration}$$+1)+".Col2$:null)"
)

The slight complication here is that the loop “currentIteration” starts its numbering at 0 and so I need to add 1 to it to conform with my column naming convention.

A second flow variable defines the output column name to be created on each iteration.

At the end of the flow, a column aggregator and a Rule engine are used to quickly determine for a group of selected columns (the FK columns) if at least one of them contains a value, and sets a new column to “1” or “0” as a result.

The second workflow in the attached puts a Column Expressions node inside the loop.

This is a little less complex than using String Manipulation, but by putting it in the loop, and dynamically determining the different columns on each iteration,
image

it means that you don’t have to write the same piece of code (modified for each column) more than once, and scales well (requiring less typing and chance of introducing errors) if you have a large number of columns.

Dynamic multi -column manipulations.knwf (58.4 KB)

2 Likes

Sorry for my late reply. I want to thank you all for your valuable help. This column expressions does the same job as the rule engines but everything in one node, just as bruno29a suggested. All the other posts were also very helpful.

Thank you all again.
keizersoz

2 Likes

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