In a table I have a column with wellcodes. The column looks like this:
A3
B7
D12
F23
AB5
AC11
AF24
Now should be the numeric part always two positions, so A3=>A03, B7=>B07 and AB5=>AB05
So I can check the lenght and if the lenght is 2 then add a 0 in the middle. If the lenght is 4 then do nothing. But in case the lenght is 3 then in case the last two digits are numbers don’t do anything, but if the 2th digit is a character then add a 0 after the two charaters.
Hopefully someone can help me out with one or another formula I can use
You can use the Column Expressions Node for that. There you can combine rules and string manipulation (and use native Javascript methods).
Script looks as follows:
// for better readability
var col = column("column1")
if (length(col) == 2) {
col[0] + "0" + col[1]
} else if (length(col) == 3) {
// If character in the middle is a letter, add padding zero
if (isNaN(parseInt(col[1]))) {
col[0] + col[1] + "0" + col[2]
} else {
col
}
} else {
col
}
Hopefully, that solved your problem.
EDIT: I have also included a second way with regular knime nodes.
It is currently part of KNIME Labs Extensions where you can preview new features that are still under development (so I would not suggest it for production use).
Go to File > Install KNIME Extensions > Enter “expression”. Then it should look something like this:
You do not have to use length rather get last two chars and if they are not forming a number that is sign you have to add 0. So with String Manipulation node take last two chars (then Rule engine is here only to copy this new column so you don’t have to work with it and can inspect intermediate result at the end). After that use String To Number node which will give you missing values where last two chars are not forming a number. Then again use String Manipulation node to add zero using string functions and length logic. At the end define which WellCode are you taking.
The workflow is clear to me except the last Rule Engine to define what wellcode to take.
Can you please explain this code per step. Is missing a command?
MISSING $Last two chars$ => $NewWellCode$
TRUE => $WellCode$
If you see Result from node before (String Manipulation node) you got 0 added to each code which is not ok. Now you need to use Rule Engine node to define what WellCode to take. So the first line means “if there is missing value in column Last two chars (actually this should be Last two chars2) take WellCode from column NewWellCode”. The one that needed modification. In Rule engine node “The first matching rule in order of definition determines the outcome” so the second line will be tested only if first did not match meaning if there is no missing value in above mentioned column. And if there is no missing you should take the original WellCode.
thanks for the explanation.
two questions:
first one: You write “you got 0 added to each code which is not ok”, but there is a 0 added to all codes.
second one: if i remove the first Rule engine node, it is still working and the outcome is the same. What is the purpose of the first rule engine node?
I miss comma
I should written: “…you got 0 added to each code, which is not ok.” meaning it is not ok to add 0 to each code.
As written in workflow the first Rule engine is only for better understanding. It only creates copy of column last two chars named last two chars2. You can remove and it will work. Only in following nodes String to Number and Rule Engine use last two chars column instead.