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.
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 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 |
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.
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.