Search Multiple Values and Find the First One

KNIME Analytics Platform
1

I have a simple problem but a solution in KIME eludes me. I have a field with a last name in as well as a persons title all in the same column. I need to strip the title off the name. Now a last name can have many different words. Spanish names alone can have up to 20+ words. So there is no way for me to tell when the name ends.

As luck would have, there is a way to tell when the title begins. The persons title begins with any one of 30 or so known strings. For example “DR”, “PHD”, “MBA” etc. But the wrinkle here is a title can consist of many differnt keywords and any one of them could be first.

Conceptually here is what I planned, but this is way to much work. Use the STRING MANIPULATION and the INDEXOF function to find where in the string each of the possible 30 keyswords is located. Then UNPIVOT all those values into rows and use a GROUP BY function to find the smallest. Rejoin the smallest value back to the original table and then use a substring function to strip it away.

There has to be a better way. I’m thinking somehow I could do this with looping but all my experimentation does not yield good results.

2

Hi @smcleod

I quickly created a workflow which should provide you with a solution for your problem, given that I understood correctly. I created a table which functions as a dictionary over which we can loop to find the position of a given title. We do this for every Name in the table.

Describes pretty well what I actually did. I just added the loops to that approach. Hope this helps!

dictionary_split.knwf (21.3 KB)

2 Likes
3

Cleaver solution. Thank you. Works great.

4

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