Look up value in same table and return as new column

Hi there,

Is there an elegant way of doing a lookup function on a table and returning a value found in that same table and adding it as a new column?

As an explanation, I have a large table of all employee information (Column A has employee ID, column B has employee full name, Column C has employee position title etc…) which I used an Excel Reader node to pull in the data and each employee has a column at the end that indicates who the employee’s manager is, however it is only the manager’s ID (Column D). I would like to do a lookup function in Knime to pull over into new columns the manager’s name and manager’s position as well whom all already exists in the table data. In Excel, this is just a very straight forward vlookup based on Columns A, B, C and using Column D as the unique ID lookup.

I can do a look-up function when there are two tables reading the data and using filter and joiner nodes but I don’t know how to do this for when I want to do a lookup in the one table and return the lookup data into new columns.

I hope that is a clear explanation and would appreciate any help.

Thank you!

Hi @ViTLe,
based on your description i assume the managers are also stored in the same table as employes and the manager id is the same as the employe id.

In this case you can use joiner node and join the same table with joining key manager id in left table and employe id in right table. To prohibit loss off data records i would set the joining mode to left outer join.

BR

6 Likes

Hi @ViTLe, as @morpheus as said, the joiner node looks like the best fit for your requirement.

As you were saying you weren’t sure how to achieve your objective when all your data is in just the one table, I thought I’d highlight that the principle is the same whether you have one table or two as there is nothing to stop you feeding your single table to both input ports of the joiner node. Your one table is then treated as if it were two totally separate input tables.

The other thing l’d point out is that on the joiner node you can specify the columns that you want returned by each side of the join so that way you don’t get all columns appended for both the left and right tables. You don’t need to use additional column filter nodes!

Finally, there is another node that can be useful for quick lookups of single values, and that is the Cell Replacer. Although I think the joiner is more appropriate in this case, I thought I’d mention it. In spite of its name, the Cell Replacer node is capable of appending a column based on a lookup from another table (or the same table if you connect it to both inputs). It can only add one column at a time though and the lookup has to be based on a single column value.

3 Likes

Hi all-mighty Morpheus,

It worked! thank you! I didn’t realise you could use a joiner to join basically the same table. That’s good knowledge to know.

Thanks again :slight_smile:

2 Likes

Thank you Takbb! I tried the joiner node method and it works perfectly and I am sure my manager will give me another piece of work some day where I will make use of Column Replacer. Really appreciate both your help! I am very new to Knime. I think I have spent a total of 7 hours using it.

1 Like

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