Lookup from one column and match with another identical column

[psycho]

I have below table with employee data. I want to get the parent position id which is in RED color if a parent position is available. Can someboady help me with this issue please? it is urgent

[bruno29a]

Hi @psycho , you can do a left join to itself joining on ParentPositionVersion = PositionVersion

If you shared the data as text where I can copy and paste from, I would have put something together for you. I’m not about to go type these records one by one

[psycho]

Hi Bruno, this is the file.
KNIME HELP.xlsx (10.3 KB)

[bruno29a]

Thanks @psycho , it looks like the file you shared is a bit different from what you showed. In the file’s case, it looks like parentPositionVersion could match to either positionName or positionVersion.

Something like this would do:

Input (almost same as what you have in your file):

You will notice that what you have for ID 10 is actually CE0 (the last character being a zero, and not an O). I manually added CEO as ID 100. So the CEO should match with 100, and not 10 in my case.

Just do a left join on either of the 2 columns I mentioned:

And in the Column Selection section, I choose all the columns from the first table, and the positionID of the second table:

Results:

You can of course rename the column “positionID (right)” to “ParentPositionID” with a Column Rename

[psycho]

It worked. Thank you Bro

[bruno29a]

You are most welcome @psycho , happy to help