Perform a Vlookup and return Values

Hi All,

I'm new to Knime.  I'm thinking in term of vlookup in Excel.  I have two data sets from two tables A and B.  What I would like to do is return UserId from Table B when Country (Assigned To) contains the Country in Table A.

 

Appreciates the help.

 

  Table A  
Lead Id Country UserId
1111 Canada 1
2222 Peru 2

 

 

  Table B    
UserId Country (Assigned To)    
1 United States, Canada    
2 Brazil, Peru, Chile    
3 Mexico    

 

 

Hi Dan,

I guess Table A is how the finale table should look like as it already contains a column named UserId.

So how do we get there? If you search for vlookup you will find many threads in this forum which suppose many different ways to get it done. The most common ways are either the joiner node (https://www.knime.com/blog/migrating-from-excel-to-knime-analytics-platform) or a cell replacer node.

In this case I'd go with the Joiner node approach, but you can't join directly on the Country columns of the tables, because the column in Table B can contain more than one country. Instead use the Cross Joiner node to get the cartesian product of both tables.

Next you can use a String Manipulation node to check for each row, whether the value of the Country column is contained in the column Country (Assigned To). I did it with this Expression: regexMatcher($countryAssigned$, join(".*", $country$, ".*"))

Afterwards the exclude all rows with false value in the newly created column with a Row Filter node and last exclude all unnecessary columns you  don't want to have in your finale table with the Column Filter node.

I hope that helps.

Cheers,
Marten

1 Like

Hello,

I am having a similar issue where I want to apply a VLookup function to Knime but am not able.

I have tried the above method but it hasn’t worked for me. I wondered if I might have some advice.

This is my left table:

This is my right table:

I want to search the unique concatenate column of my right table to find the Fragment index of my left table (Column 1) and then return the pref name from table 2 for each Fragment index.

e.g.

Fragment index | pref_name |
1 | Cannabinoid CB2 Receptor |
2 | HERG |

Here is what I’ve tried:

and this is the error message I got:
image

I hope this makes sense, any help would be greatly appreciated :slight_smile:

Thanks!

I think you need to “escape” some quotation characters in your regex with backslashes
br

Hi Daniel,

Thanks for your response! However I’m not sure what you mean - do you mean like this:

regexMatcher($Unique concatenate$, join(., $Fragment Index$, .))

I tried this but it errored again unfortunately.

Thanks for your help!

Hi @gretasugrue ,

I think he means regexMatch($Unique concatenate$, join(“\.\", $Fragment Index$, "\.\”) possibly.

J.

Hello all,

I am also having the same issue. I would like for the end result to replace all cells in the 2nd Table turning it to the 3rd table. So basically vlookup/query Table 2 - Value on 1st Table and when returning it, replace the value with the Name corresponding to that Value in the same row of Table 1. This hundreds of times in both ways, cols and rows.

Name car location color Serial
Matt Buick Ohio black 012001022144
Bob Ford Michigan blue 012001022143
Tom Chevy Indiana white 012001018949
Brad Honda Oregon yellow 012001018550
Jenny Toyota Florida silver 012001027818
Maria Tesla California red 011901031631
Jill Fiat Washington white 011901031104
Josh Jaguar Oregon green 011901031824
William BMW Maine brown 012001016885
Target_1 Target_2 Target_3 Target_4
012001022144 011901031104 012001027818 011901031104
012001022143 011901031631 012001022144 012001018949
012001018949 011901031824 012001022143 012001022144
012001018550 012001016885 012001018949 012001022143
012001027818 012001018550 012001018550 012001018949
011901031631 012001018949 012001016885 012001022144
011901031104 012001022143 011901031824 012001027818
011901031824 012001022144 011901031631 012001022144
012001016885 012001027818 011901031104 012001022143
Target_1 Target_2 Target_3 Target_4
Matt Jill Jenny Jill
Bob Maria Matt Tom
Tom Josh Bob Matt
Brad William Tom Bob
Jenny Brad Brad Tom
Maria Tom William Matt
Jill Bob Josh Jenny
Josh Matt Maria Matt
William Jenny Jill Bob

Thanks a lot
J.

Hi @jarviscampbell if you look back at your previous forum thread, although it was a different issue/case, one of the solutions provided to you can be applied in this new issue/case of yours, specifically the one by @duristef :

Their suggestions of using the Joiner Node or the Rule Engine (Dictionary) Node are both applicable.

Alternatively, as pointed out by @marten_kose above, you can also opt for the Cell Replacer Node. In this case, your dictionary table will be Table 1.

If you have problems implementing the solutions, let me know. Thank you.

1 Like

Hi @badger101,

I was able to make it work with the Cell Replacer Node which seems to be a much easier, cleaner and perfect for what this example.

Thanks for your reply, I appreciate you.

J.

2 Likes