SQL to Knime Case

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?

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:

Br,
Ivan

1 Like

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

3 Likes

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

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”

image

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.