how to delete and update the last digits of a code

Hi.
I’ve been trying to implement an algorithm in Knime, but, so far, I couldn’t.
I have records in a column of my database. They are numerical codes sperated by dots, kept in string type. Code example: 537.45.50.10.11.10.56.01.01

Algorithm is:
Check the code. If it is longer than 6 parts, cut it to 6
Then, the last(6th) part of the new code will either be .01 or 1 more of the maximum number in the column among the the codes of the same length.
For example, code above is 537.45.50.10.11.10.56.01.01 It will either be shortened to 537.45.50.10.11.01 or max(6th part)+1, if there is any.

Thank you.

Hi @frtvrl and welcome to KNIME Community Forum,

Would you please provide us an example list of codes and the updated version of them so I can make sure I have understood your point perfectly?

:blush:

Thank you, @armingrudd . It’s a wonderful community you guys have in here.

NOTE: It actually has to be the 5th part, not 6th. Sorry, my bad.

codeknime

When a code is longer than 5 parts, I want to cut it to 4 and then add 5th part sequentially; either from 01 or from the maximum number among the codes with same length. So, If there is a 5 part-long code ending with 50, new 5 part-long codes that I will update should start with 51.

I hope I could explain my thought clearly. I’ve been overloading my brain for the last week but nothing came out really.

So if there are only 5 parts, it shouldn’t get changed.

Here is the workflow to do so:
41652.knwf (27.9 KB)

What I did is: First, I split the values into the first 4 parts, 5th part and the rest. Then I generated some ordinal values (rank) for each group of the first 4 parts. Finally, I merged the first 4 parts with the 5th part if the third split (the rest) is empty or with the rank value if there is some value in the third split.

I have used the Column Expressions node to minimize the number of nodes needed and did several steps in a single expression. If you’ve got issues to install new extensions let us know and we can do it without this node as well.

:blush:

4 Likes

Actually, I would be really happy to see the solution without this node because apparently, my network connection doesn’t allow Knime to install the necessary extension.

41652-base nodes.knwf (30.5 KB)

1 Like

Thank you so much, @armingrudd for your help. The workflows you prepared and the explanations you wrote helped me understand the issue clearly.

Edit: Website lets me tick only one solution although both of them are absolutely fantastic.

1 Like

Hey @armingrudd hello again.

I was wondering if it is possible to make the flow check the whole codes to generate new codes by checking the codes in another column.

Can you explain your point a bit more?

@armingrudd
There are original master codes in the codes column. They can be listed if I write SQL query “select codes from codetable where codeoid=0” The codes that I want to change has codeoid=10. I want workflow to check original codes before changing the codes with codeoid=10 so that there wouldn’t be same multiple codes.

For example:

master code is 150.01.01 and code with codeoid=10 is 150.01.01.01.08. If I do the workflow right, the new code would be something like 150.01.01.XY.

Codeoid=0
150.01.01.01
150.01.01.02
150.01.01.03

Codeoid=10
150.01.01.01.01 would be transformed to 150.01.01.01.01
150.01.01.01.02 would be transformed to 150.01.01.01.02
150.01.01.01.01.01 would be transformed to 150.01.01.01.03
150.01.01.01.01.01.01 would be transformed to 150.01.01.01.04
150.01.01.01.01.01.02 would be transformed to 150.01.01.01.05
150.01.01.01.02.01 would be transformed to 150.01.01.02.01
150.01.01.01.02.02 would be transformed to 150.01.01.02.02

Ex2:
codeoid=0
150.01.04.01
150.01.03.01.01
150.01.03.01.02
150.01.03.02
150.01.03.04.01
.
.
.
Codeoid=10
150.01.04.01.01.01 would be transformed to 150.01.04.01.01
150.01.03.01.01.01.01 would be transformed to 150.01.03.01.01.01
150.01.03.01.01.01.02 would be transformed to 150.01.03.01.01.02
150.01.03.01.01.01.02.01 would be transformed to 150.01.03.01.01.03
150.01.03.01.01.01.02.01.01 would be transformed to 150.01.03.01.01.04
150.01.03.01.01.01.02.01.02 would be transformed to 150.01.03.01.01.05
150.01.03.04.01.01.02.01 would be transformed to 150.01.03.04.01.01

hey, @armingrudd sorry to bother you. I think I wasn’t as clear as I wished to be when I wrote what I had on my mind.

What I actually try to achieve:

There are records in my DB with varying lengths(3,4, and 5-part lengths). They are my main codes and I want the new codes to look at them be changed. It would look like this if I make it:
mainer

I want it to look at the master records, not the split_0.

I hope I could explain it :blush:

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