How to sum values based on a rule and create a check column

Hi everybody,

I’m tring to create a workflow or a Java/Python script for getting a sum based on NUMBER when type is A or B.

If like in the cases of documents 1, 2, 3 and 4 the sum of A + B = 0, then OK is appended in the NEW COLUMN

If, like in the cases of documents 5 we have just A but not the counterpart B then CHECK is appended in the NEW COLUMN

If, like in the cases of documents 6, A and B don’t sum 0 then CHECK is appended in the NEW COLUMN.

When type is not A nor B then nothing is appended to NEW COLUMN

The original excel file contains further columns and I would like to maintain them in the output

NUMBER TYPE VALUE NEW COLUMN
1 A 10 OK
1 B -10 OK
2 A 20 OK
2 B -20 OK
3 A 40 OK
4 A 50 OK
3 B -40 OK
4 B -50 OK
5 A 30 CHECK
6 A 35 CHECK
6 B -30 CHECK
44 C 11
55 C 12
66 C 55
77 C 99
13 C 99
22 C 978
15 C 22

Can you give me an advice?

Thanks and happy new year!

Robert

hi @bermq,

please have a look here on KNIME Hub…

A group by node followed by a rule engine node do the trick for you.
You may drag and drop the workflow symbol from the hub to your KNIME AP window.

Hope that helps, Greetz, Tommy

You may

2 Likes

Hi Tommy!

Many thanks for the quick answer.

What would be the expression in the Rule Engine?

Sorry, I didn’t drag the file, it works!

1 Like

Hi Tommy,
Sorry, just a last thing; I see that for the documents with both types A and B the lines are reported twice.
It’s possible to avoid it in the Joiner or shoul I create a Duplicate Row Filter?

hi @bermq,
I just attached the new information of NEW COLUMN to the input table - that’s the joiner node.
However, you may use the output of the rule engine in order to have your documents unique.
Or, as you proposed include a step with the duplicate row filter.
Greetz, Tommy

1 Like

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