SQL to Knime Case

[yoshiki3]

Hi there, I got a code that I need to run on a node,

If the first two digits are: “20”,“11” or “22”, then Provider_type =’ E’’
If the first two digits are “23” or “24”, then Provider_type = ‘L’

SQL code below:

case
when substr(table.column,0, 2) in (‘20’,‘21’,‘22’) then ‘E’
when substr(table.column,0, 2) in (‘23’,‘24’) then ‘L’
else ‘NA’
end

What nodes/code would you recommend?

[ipazin]

Hello @yoshiki3,

you can use Column Expressions node. There you’ll find both case syntax and substr() function. Also you can use classic if/else which might be simpler for your case.

Check here workflow example with if/else:

If Else in Column Expression – ipazin

Br,
Ivan

[ArjenEX]

Just wondering, the solutions provided in the previous topic that you created with the exact same question were not sufficient?

Using Case, and subsr in Knime

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?

[yoshiki3]

Arjen, sorry if this troubled you. I was only looking for alternate solutions. Best regards

[takbb]

Hi @yoshiki3 ,

For your specific example (in both your questions) I would probably opt for the Rule Engine approach.

If you are still looking for an alternative, you can use the “almost undocumented” syntax of the String Manipulation node to achieve the desired result.

i.e. Assuming you have a table with “column1” containing your data, then the following in String Manipulation would also return the required result:

regexMatcher($column1$,"20.*|11.*|22.*" ).equals( "True" )
?"E"
:regexMatcher($column1$,"23.*|24.*" ).equals("True" )
?"L"
:"NA"

I think it can also be written as

regexMatcher($column1$,"20.*|11.*|22.*" ) == "True" 
?"E"
:regexMatcher($column1$,"23.*|24.*" ) == "True" 
?"L"
:"NA"

The syntax for the conditionals is as follows:

condition ? result-if-true : result-if-false

In this case it says that if the column matches the regex beginning 20, 11 or 22, then return “E” otherwise if it matches the regex beginning 23 or 24 return “L” otherwise return “NA”