Adding a zero depending on lenght of string

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.

adding-a-zero-depending-on-lenght-of-string.knwf (25.0 KB)

3 Likes

Hi Johannes,

the Column Expression Node is not available for me. Did a search but nothing came up.

Is there an other way to solve this issue? Or to get the Column Expression Node

Thanks,
Ron

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:

Then install KNIME Expressions and restart.

Hi Johannes,

I don’t get these options. Only this
image

Source for KNIME Math Expression (JEP)

Isn’t there an other way to achieve the same?

Thanks
Ron

Hi there!

If you would like more “Knime traditional” approach here is a solution :slight_smile:

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.

Workflow is attached.

Adding zero to Code.knwf (13.0 KB)

Knime Expressions node is a really cool node and is available from 3.6 version :wink:

Br,
Ivan

3 Likes

@ipazin Took me 9 nodes to do it the traditional way. Your approach just 5, not bad :+1:

You are right, the Column Expressions node is only available from 3.6+.

1 Like

Hi Ivan and Johannes,

This workflow is doing the job. I have to look to the details to fully understand how it works.
But both thanks for the effort

Ron

Hi Ivan,

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$

Thanks,

Ron

Hi Ron!

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.

Hope I managed to explain it right :wink:

Br,
Ivan

Hi Ivan,

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?

Thanks,

Ron

Hi Ron,

  1. I miss comma :smiley:
    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.
  2. 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.

Br,
Ivan

Hi Ivan,

ha,ha, this explains it. What a comma can do.

Thank a lot again.

Ron

1 Like