Applying a formula, but only for certain rows matching specified criteria

[sroberts]

Hi all,

I have columns like Room Number and Customer and the result of a Regex match. I would like to apply a mathematical function on the Regex match only for a list of specified room numbers and customers.

Alternatively, the Regex function could only apply if it matches the specified columns.

I’m still learning Knime and Regex so I’m not super familiar with this kind of workflow yet

[elsamuel]

I have columns like Room Number and Customer and the result of a Regex match. I would like to apply a mathematical function on the Regex match only for a list of specified room numbers and customers.

If you have a list of conditions to be tested, then compiling them with a Rule Engine node or using a series of if...else statements in a Column Expressions node should work. Can you provide more details about the logic you’re trying to implement? Sharing the workflow and data would help.

[sroberts]

Just so you understand what I am doing, in certain rooms, the amount produced has been calculated incorrectly. I created a helper column that indicates what the amount produced should be divided by to get the correct quantity, but this column still has numbers for rooms that do not have this calculation error.

I’m still learning the syntax here, right now I am just trying to apply the math to specified rooms. So far I am only able to create a new column with the “True” condition. If I enter in the formula I get an error.

($Room$ LIKE “7” OR $Room$ LIKE “12” OR $Room$ LIKE “15” OR $Room$ LIKE "16"OR $Room$ LIKE "17"OR $Room$ LIKE "27"OR $Room$ LIKE "31"OR $Room$ LIKE “32”) => “TRUE”($Room$ LIKE “7” OR $Room$ LIKE “12” OR $Room$ LIKE “15” OR $Room$ LIKE "16"OR $Room$ LIKE "17"OR $Room$ LIKE "27"OR $Room$ LIKE "31"OR $Room$ LIKE “32”) => “TRUE”

Here is the math function I would like to use.
$Amount Produced$/$Match 0: Full Match$

Here is some example data

Room:32 (Data has calculation error)
Amount Produced: 145000
Helper: 10
Expected Result: 14500

Room:8 (Data is clean)
Amount Produced: 145000
Helper: 10
Expected Result: 145000

[elsamuel]

If I enter in the formula I get an error.

What is the error that you get?

($Room$ LIKE “7” OR $Room$ LIKE “12” OR $Room$ LIKE “15” OR $Room$ LIKE "16"OR $Room$ LIKE "17"OR $Room$ LIKE "27"OR $Room$ LIKE "31"OR $Room$ LIKE “32”) => “TRUE”($Room$ LIKE “7” OR $Room$ LIKE “12” OR $Room$ LIKE “15” OR $Room$ LIKE "16"OR $Room$ LIKE "17"OR $Room$ LIKE "27"OR $Room$ LIKE "31"OR $Room$ LIKE “32”) => “TRUE”

1. Did you mean to copy and paste the same thing twice?

2. The LIKE function only works for strings. I imagine that Room column is formatted as integer. If this is the case, you’ll get an error that the Expression before ‘LIKE’ is not a string. With numbers you should just use ‘=’. The false condition can be acheived using the expression TRUE => *outcome*.

   For example:

   $Room$ = "7" OR $Room$ = "12" OR $Room$ = "15" OR $Room$ = "16" OR $Room$ = "17" OR $Room$ = "27" OR $Room$ = "31" OR $Room$ = "32" => TRUE
TRUE => False

Here is the math function I would like to use.
$Amount Produced$/$Match 0: Full Match$

Where is $Match 0: Full Match$ coming from? What values does this column contain?

Can you upload the workflow?

[sroberts]

Thank you for reading over this.

1. Oops!

2. Room is actually a string. Some of the rooms are formatted like 7, 8, 9 - some are formatted like “6 Primary”

3. Unfortunately proprietary information limits what I can share. $Match 0: Full Match$ is the result of a regex expression. It gives me a number that the “Amount Produced” should be divided by using information elsewhere. Both of these columns are integers. I am using a rule engine node to accomplish the task of trying to apply the formula only to the specified rooms. I am not using flow variables or anything like that. For all intents and purposes I’m going right from the data that I have to the rule engine. Maybe this is my issue? I’m trying to understand proper workflow here.

For example
$Amount Produced$ = 10000
$Match 0: Full Match$ =10

Expected Result = 1000

The below expression results in the error ’ Garbage at end of rule detected

($Room$ LIKE “7” OR $Room$ LIKE “12” OR $Room$ LIKE “15” OR $Room$ LIKE "16"OR $Room$ LIKE "17"OR $Room$ LIKE "27"OR $Room$ LIKE "31"OR $Room$ LIKE “32”) => $Amount Produced$/$Match 0: Full Match$

[elsamuel]

Ok, so the “Helper” column from your previous post is actually called "$Match 0: Full Match$

Room is actually a string. Some of the rooms are formatted like 7, 8, 9 - some are formatted like “6 Primary”

That’s good to know.

The below expression results in the error ’ Garbage at end of rule detected

($Room$ LIKE “7” OR $Room$ LIKE “12” OR $Room$ LIKE “15” OR $Room$ LIKE "16"OR $Room$ LIKE "17"OR $Room$ LIKE "27"OR $Room$ LIKE "31"OR $Room$ LIKE “32”) => $Amount Produced$/$Match 0: Full Match$

Now I see the issue. As far as I’m aware, the Rule Engine node doesn’t work like this; you can’t do math after the arrow. Try using the Column Expressions node:

if (or(column("Room") == "7", 
       column("Room") == "12",
       column("Room") == "15", 
       column("Room") == "16", 
       column("Room") == "17",
       column("Room") == "27",
       column("Room") == "31",
       column("Room") == "32"))

  {
    column("Amount Produced")/column("Match 0: Full Match")
    }

else
  {
    column("Amount Produced")
    }

You can use this expression to populate a new column, or replace an existing column.

[sroberts]

Thank you so much! One last thing -

Sometimes the quantity in $Amount Produced$ is 0, which results in a NaN error. I would like it to read 0.

Also sometimes the $Match 0: Full Match$ is null, in which case the expression results in ‘Infinity’. I would rather it just show the ‘Amount Produced’ in that case.

Do you know the code I could use to accomplish that or point me in the right direction?

[elsamuel]

Sometimes the quantity in $Amount Produced$ is 0, which results in a NaN error. I would like it to read 0.

I’m not sure I understand what’s happening here. If the quantity in “Amount Produced” is 0, then the result of dividing by “Match 0: Full Match” will be 0.

Also sometimes the $Match 0: Full Match$ is null, in which case the expression results in ‘Infinity’. I would rather it just show the ‘Amount Produced’ in that case.

I think it’d be easiest to add some conditions to the if...else expression

Is this the logic you’re aiming for?

If "Amount Produced" is not 0, 
and "Match 0: Full Match" is not missing
and "Room" is either 7, 12, 15, 16, 17, 27, 31, or 32

then create a new column using the value "Amount Produced" ÷ "Match 0: Full Match"

otherwise copy the value from "Amount Produced"

The code for this would be:

if(and(column("Amount Produced")!= 0, not(isMissing(column("Match 0: Full Match"))),
   (or(column("Room") == "7", 
       column("Room") == "12",
       column("Room") == "15", 
       column("Room") == "16", 
       column("Room") == "17",
       column("Room") == "27",
       column("Room") == "31",
       column("Room") == "32"))))
       
{column("Amount Produced")/column("Match 0: Full Match")}

else 
{column("Amount Produced")}