Perform a Vlookup and return Values

[Dan]

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

 

 

[marten_kose]

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

[gretasugrue]

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:

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

Thanks!

[Daniel_Weikert]

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

[gretasugrue]

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!

[jarviscampbell]

Hi @gretasugrue ,

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

J.

[jarviscampbell]

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.

[badger101]

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 :

Column expressions dual or multiple functions same line

hi @jarviscampbell @elsamuel suggested a simple and clear expression. If you prefer to use Knime nodes you have (AFAIK) two ways Let’s say your data table is like this one [immagine] the Joiner node Create a table like this one [immagine] and join it to your data table on the “colour” column the “Rule engine (Dictionary)” node. Create another table with the rules [immagine] and apply the rules to the data table In this workflow you find these two solutions KNIME_project7.knwf …

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.

[jarviscampbell]

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.