Using Case, and subsr in Knime

[yoshiki3]

Hi, I got a string that starts with numbers,
If the code starts with “10”,“11” or “12”, then ORDER TYPE = ‘VI’
If the code starts with “13” or “14”, then ORDER TYPE = ‘MI’

case when substr(table.column,0, 2) in (‘10’,‘11’,‘12’) then ‘VI’
when substr(table.column,0, 2) in (‘13’,‘14’) then ‘’
else ‘NA’ end
How can I implement this in Knime?

[ArjenEX]

Hi @yoshiki3

One way to solve this is to determine the index of the mentioned codes by using the following code in a Column Expression node:

if (indexOf(column("column1"),"10",0) == 0 || indexOf(column("column1"),"11",0) == 0 || indexOf(column("column1"),"12",0) == 0 ) {
    "VI"
} else if (indexOf(column("column1"),"13",0) == 0 || indexOf(column("column1"),"14",0) == 0) {
    "MI"
} else  {
    "NA"
}

Result:

[yoshiki3]

Thanks a lot, you are a life saver!
Is the syntax somehow similar to Java?
(It’s been ages since I did any coding… )

[ArjenEX]

If it helped you please mark it as solution so that others can also benefit from this more easily

Depending on which node you use it’s indeed either Java or Javascript. Click on the Function button to get all available options and the corresponding syntax with examples.

[denisfi]

Hi,

You can use Rule engine node too to test/check your column data and set a new column with a value.

I used the ROWID column as an example, but you’ll use the “column1” from your flow.

$$ROWID$$ MATCHES “^1[01]” => “VI”
$$ROWID$$ MATCHES “^1[34]” => “MI”
TRUE => "NA

“TRUE” is a defined information if all others test don’t pass.

Thanks,

Denis

[Daniel_Weikert]

or check for 10/11/12 beginning with regex
^(10|11|12).*

[denisfi]

Hi,

It’ll bring the same result, I just set the decimal as fixed value and [012] with one digit to validate, I don’t need to bring .* to select all the information because the “^” says at the begin one.

Buts, it’s ok, the result will be the same at all… And you won’t need to create a formula to test it, easier mode on…

Tks,

Denis