Loop String Manipulation Replace

Hallo community,

I’ve 2 Tables.
In 1 Table is my data with various columns. (Prename, Surname, Age, Ctry)
The Other Table has one column with expressions like Dr., Prof., Van, Von,
and so on.

I tried to search in the first table the expressions from the second table and manipulate that.

Example

Table 1
Dr. Hans | Wagner
Heinrich | Boerstel
Dr. Med. Franz | von Dietermeier

Table 2
Dr.
Prof.
Med.
Von
Van

Result Table 1
Hans | Wagner
Heinrich | Boerstel
Franz | von Dietermeier

Unfornately my WF is incorrect.
I tried a loop Table row to variable with second table + multiple column string manipulation.

The Loop End collect many version of the tables :frowning:
I need only one final result :frowning:

Does anyone have an idea ?

Hello @Paddymaster,

and your idea is to replace all expressions from Table 2 that come up in Table 1 in those two columns? If so seems “von” was left by accident in result table. Can you share your workflow?

Br,
Ivan

Sanctions_Test.knwf (28.6 KB)

Well. Here’s an example Workflow. I think its also necessary to Ltrim the result.

Hello @Paddymaster,

this seems like a something others could benefit from so I have created an example which you can find on KNIME Hub:

I decided not to use loop and instead used regex in String Manipulation (Multi Column) node to perform same operation on multiple columns. Also instead of Joiner you can use new node to compare two tables - Table Difference Finder node.

Check it out and if any questions feel free to ask.

Br,
Ivan

1 Like

Well yeah. It’s a good solution. But in my Case. The rows are different between the two tables. Thats why i use the Joiner Node.

Also one question. How is it possible to use this in a loop without aggregate tables ?

And also a Question. If i do an Inner Join with Joiner node i get the positive matches.
How’s possible to get the negative matches ?

I had a similar problem myself for a client I contracted on. They had a list of keywords they considered to be part of the name prefix. The list was substantial. It included miltary ranks, religious titles, poltical titles etc and multiple languages so it needed to handle Mr., Heir and Singnor. What I did was looped through each word of the name field comparing it against the know list of prefixes. When I found a word not know to be a prefix, I marked that as the spot where the actual name began and removed the rest of the string before that into the name prefix. Here is a sample of how I did that.
NamePrefix Parse.knwf (173.9 KB)

Hello @Paddymaster,

if you want to join then check out Joiner (Labs). There you can get unmatched rows as well.

What is a loop without aggregate tables? And why do you want to use loop?

Br,
Ivan

Well. Dont know :wink: but if i want use a loop. How is it possible to use one input table and get one output table.
In my first example it wasn’t possible to get out one table. I just got one big table which iterated added the common tables

PS. Joiner Labs is perfect :slight_smile:

1 Like

Hello @Paddymaster,

If I got you right you would have to use a recursive loop to achieve. Look for workflow examples on KNIME Hub.

Glad you like it :slight_smile:

Br,
Ivan

Well; I think recursive loop is the right node. I’ll test it next time

1 Like

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