IF ELSEIF Condition to change strings?

[mr2mike]

I need some help getting this all done in one node? Is that possible? I have it built in Tableau but am moving away from Tableau for data manipulation and building the sources in KNIME.
Couldn’t quite get column manipulation working for me. String manipulation seems to be one node at a time which makes it a hassle for me.
Here is the Tableau code.

Column names are BU_ID and GL_GRP

IF LEFT([BU_ID],2)=“AB” THEN “ABANDONMENT”
ELSEIF LEFT([BU_ID],2)=“DR” AND LEFT([GL_GRP],3)=“IDC” THEN “DRILLING”
ELSEIF LEFT([BU_ID],2)=“DR” AND LEFT([GL_GRP],3)=“ICC” THEN “COMPLETION”
ELSEIF LEFT([BU_ID],2)=“DR” AND LEFT([GL_GRP],3)=“TCC” THEN “COMPLETION”
ELSEIF LEFT([BU_ID],2)=“EW” THEN “EARTHWORKS”
ELSEIF LEFT([BU_ID],2)=“EN” THEN “ENVIRONMENT”
ELSEIF LEFT([BU_ID],2)=“FW” THEN “WELLSITE”
ELSEIF LEFT([BU_ID],2)=“FC” THEN “FACILITIES-MAJOR”
ELSEIF LEFT([BU_ID],2)=“FF” THEN “FACILITIES”
ELSEIF LEFT([BU_ID],2)=“WO” THEN “WORKOVERS”
ELSEIF LEFT([BU_ID],2)=“FP” THEN “PIPELINES”
ELSE “OTHER CAPITAL”
END

[mlauber71]

@mr2mike you could use Column Expression like in this example at the top:

simple inclusion and exclusion by rules in columns. Also apply a collection...

[mr2mike]

I definitely could. Can someone explain what the curly brackets and the numbers between is doing in the expression?

Also how does that handle the AND statements on the first 3 IF items?

[takbb]

Hi @mr2mike , the syntax for the Column Expressions node uses javascript, which is also similar to java syntax.

In this syntax the curly brackets mark a block of lines, and acts the same way as indentation in python, and begin end in some other languages.

The numbers in the previous example were simply the values to be returned when conditions were met

The condition itself must be placed inside round brackets. So the general form is

if (condition)
{
do or return something
}
else
{
do or return something
}

There are a couple of options for performing AND and OR in Column Expressions.

There are and(cond1,cond2,cond3) and or(cond1,cond2,cond3) functions

Alternatively you can use java notation:
AND is represented by &&
OR is represented by || ( a pair of “pipe” symbols)

Your first lines…

IF LEFT([BU_ID],2)="AB" THEN "ABANDONMENT"
ELSEIF LEFT([BU_ID],2)="DR" AND LEFT([GL_GRP],3)="IDC" THEN "DRILLING"

would equate to something like this:

if (substr(column("BU_ID"),0,2)=="AB")
{
    "ABANDONMENT"
}
else if (and(substr(column("BU_ID"),0,2)=="DR", substr(column("GL_GRP"),0,3)=="IDC") )
{
    "DRILLING"
}

or alternatively:

if (substr(column("BU_ID"),0,2)=="AB")
{
    "ABANDONMENT"
}
else if (substr(column("BU_ID"),0,2)=="DR" && substr(column("GL_GRP"),0,3)=="IDC") 
{
    "DRILLING"
}

and you’d then continue to nest your remaining conditions…

It can also be done in a String Manipulation node, which uses a variation on Java’s “ternary operator” syntax which takes the form:
condition?value-if-true : value-if-false and these can also be nested

The whole thing then needs to be wrapped in a string( ) function

string(

substr($BU_ID$,0,2).equals("AB")
 ?"ABANDONMENT"
 :substr($BU_ID$,0,2).equals("DR") && substr($GL_GRP$,0,3).equals("IDC")
   ?"DRILLING"
   :"and so on...."

)

nb because String Manipulation uses Java and not JavaScript, equality testing of strings has to be performed using the .equals() notation rather than == or else the test will fail to match

[mr2mike]

@takbb
Thank you so much for this explanation. Helps immensely and I’ll be able to use this method for other things now that I understand what’s happening in the syntax. Appreciate it!