@gwhall6 thank you for the insight. I too am converting from Alteryx to KNIME but do not have a deep coding background. I absolutely love the Formula tool in Alteryx for its power and ease of use. What is the KNIME equivalent and how does one go about writing ElseIF statements, IF statements etc?
As a note - I found the Column Expressions node and would like to know how we might write an IF statement in it.
@izaychik63 awesome! thank you! I will give this a try
Hi - you can also use the “Rule Engine.” Check it out and let me know if you have questions how to use it.
@gwhall6 If you could show me how to use the Rule Engine or Column Expression node for something like the below that would be awesome:
If Contains([Feeder Requisition Number], “Feeder”) or Contains([Feeder Requisition Number], “Evergreen”) THEN “Parent”
elseIf Contains([Feeder Requisition Number],“Fall”) THEN “Unknown”
elseIf ([Feeder Requisition Number] =“H1b”) THEN “Unknown”
elseIf IsNull([Feeder Requisition Number]) THEN “Individual”
elseIf Contains([Feeder Requisition Number], “Q”) THEN “Unknown”
elseIf Contains([Feeder Requisition Number], “1”) THEN “Child” else “Unknown” endif
In Rule Engine it will looks approximately this way:
IF [Feeder Requisition Number] LIKE “Feeder”) or [Feeder Requisition Number] LIKE “Evergreen” => “Parent”
IF [Feeder Requisition Number] LIKE “Fall”) => “Unknown”
IF [Feeder Requisition Number] =“H1b” => “Unknown”
IF MISSING [Feeder Requisition Number] => “Individual”
IF [Feeder Requisition Number] LIKE “Q”) => “Unknown”
IF [Feeder Requisition Number] LIKE “1”) => “Child”
TRUE => “Unknown”
Like operators assumes stars around context. Do not know why they are not visible.
@izaychik63 thank you that helped a ton. The logic is a lot different but it works!
Hi,
sorry that I missed this post concerning the Column Expressions node. In another thread (see here) I broke down the syntax of the Column Expressions and Variable Expressions node and how to use it.
I’ve already mentioned that it is possible to use loops (for, while) and if-else statements.
As previous posts already mentioned, you have the following syntax:
if(**state**) {
// do this, if **state** evaluates to true
} else {
// do that, if **state** evaluates to false
}
So this is rather a programmatical approach of a problem. The if-else statement creates a branch in the executing expression, that is executed from top to bottom. As soon as the if statement is reached, the state is being evaluated. Depending on that evaluation either the if block (enclosed by the following ‘{’ and ‘}’) is entered or the else block (with it respective braces). Thus, the if-else statement yields a branch in the usual evaluation flow, where either one of the blocks is evaluated (but not both).
While you are able to leave the else block empty, you can also completly skip it, e.g.
// previous code
if(**state**) {
// do this if state evaluates to true
}
// do this independently of **state**
Instead of nesting if-else-blocks like this
if(**state1**) {
// do this, if **state1** evaluates to true
} else {
if(**state2**) {
// do this, if **state1** evaluates to false and **state2** evaluates to true
} else {
// do this if neither **state1** nor **state2** evaluate to true
}
}
You can write
if(**state1**) {
// do this, if **state1** evaluates to true
} else if (**state2**) {
// do this, if **state1** evaluates to false and **state2** evaluates to true
} else {
// do this if neither **state1** nor **state2** evaluate to true
}
You can add as many else-if blocks as you like and you can always skip the last else block.
Now concerning the actual if-statements (denoted by state, state1, and state2 in the previous examples):
The Column Expressions and Variable Expressions nodes provide different functions that evaluate a specific input into a boolean value (true of false). Some useful functions, which can usually be used among all types of input are grouped under the category Logical. This group contains functions like and(val1,val2,…), which evaluates to true if all provided values (or nested functions) evaluate to true, or(val1,val2,…), which evaluates to true if at least one provided value (or nested function) evaluates to true, and equals(val1,val2), which evaluates to true, if val1 equals val2.
Depending on the category there might be more logical functions (usually denoted by the Return Type: Boolean). In addition to that, you can also compare different types (based on the column type) accordingly, as for numbers we have:
Note, that this, while still easy to use for numbers, might be more advanced for other non-numerical types.
The difference to the Rule Engine node is explained rather simple. While the Rule Engine node might provide its own easy-to-use syntax its main purpose is to match user specified rules to specific values. This means that the syntax looks as follows:
**state** => **value**
While this can usually be easily translated into the Column Expressions syntax:
if (**state**) {
**value**
}
you can, instead of simply return a value (which will be assigned to the output column) do calculations depending on the evaluation and continue with your calculations.
As mentioned in the other thread the syntax is based on JavaScript and thus, the if-else-statements behave accordingly (and similarly to other programming/scripting languages).
I hope this answered helped a little bit to clarify, how to use these statements and what the difference between the _if-else_blocks in a Column Expressions node and the rules in a Rule Engine node are.
If there are any questions (especially concerning the Expression-nodes), don’t hesitate to mention me directly in your posts.
Cheers,
Moritz
I found my example workflow attached did not classify my example “101” correctly when I only put LIKE “1”, so I put LIKE “1” (there are stars to the left and right of 1 that when posted may dissappear) and it worked, for what that is worth. The description of the Rule Engine node will give you some added insight as well.Example for TardisPilot.knwf (6.3 KB)
Hi all,
I did just move those answers into a separate topic.
Best wishes, Iris
@gwhall6 Thank you for the example! I was able to successfully make it work. Now I just need to do some validating to ensure it’s capturing the data correctly.
@moritz.heine Thank you for the programattic approach to the expressions, the breakdown is much appreciated.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.