How to perform specific excel formula in knime

Hi there,

I need to perform the following excel formula on a column of data:

=IF(LEN(B2)>6, B2, C1)

This formula checks to see if the length in column b is greater than 6 and if it is then it returns the value from the row in column B and if the value is not greater than 6 it returns the previous value in column c. How could I perform this with Java or some other tool?

This solution might help you: Column Expressions
Cheers

Thank you for the response. I have tried this node but I don’t know how to retrieve the previous row value as designated by the “C1” value in the formula. Any suggestions?

In the Column Expressions node in KNIME 4.6, there is a column() function, and one of its optional parameters is an offset. You can use this offset to access preceding or following values in a column.

Note that this feature is new to version 4.6!

2 Likes

I have to work in 4.2.2. That would be an awesome shortcut though!

1 Like

That’s too bad. :frowning: In that case, maybe you can get there with a combination of the Lag Column and Rule Engine nodes?

Give it a try, and if you are still stuck reply back and we’ll see what we can do.

1 Like

I apologize for my ignorance but I don’t know how to actually combine those nodes. I have tried using both but I guess i just dont see how to use them both to make the above said formula. I have been trying everything I can think of

No apology necessary! Everyone’s a beginner at some point, right? :slight_smile:

Here’s one way you could do it without the Column Expressions:

2022-08-16 11_52_07-KNIME Analytics Platform

LagRuleEngineExample.knwf (10.4 KB)

1 Like

so what you previously sent wasn’t quite giving me the correct results that I am looking for. In the image, the column “new_node” contains the formula I mentioned above. Is there anyway to achieve these results?

any more suggestions would be greatly appreciated.

In that case you could try this approach:

2022-08-16 14_06_45-KNIME Analytics Platform

RuleEngineMissingValueExample.knwf (9.3 KB)

It’s always best to lead with a specific data example to get the most efficient help :slight_smile:

2 Likes

To supplements Scott’s answer in case you are working with strings, the Rule Engine formulation will not work then and you’re better off with a Column Expression checking for:

if (length(column("column2")) > 6 ) {
    column("column2")
} else {
    null
}

Apply the Missing Value node thereafter like Scott has.

1 Like

I think this iteration can be interesting:
jonhsonjm1992.knwf (54.9 KB)

The result is:
resul

@jonhsonjm1992, considere upgrade to knime 4.6!, as @ScottF commented.

Best