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
