Vlookup (Excel) type of function/workflow?

I couldn't find anyhting obvious - is there some way of doing a "vlookup" as in Excel with Knime?

The closest I came doing is to join two tables and use group by. This though needs a lot of work-arounds, as in rejoining with original table if you have a lot of other data-columns attached.  Also, it works "ok" if you have a couple hundred or thousand columns. But if it is hundred thousands or millions then it is a bit of a pain (well, that many would be in excel as well, obviously, if at all possible...).

Is there a way via looping? Or some other function I haven't found yet? I am not good with java or perl, I imagine a snippet would be an alternative if you know what you are doing.

 

The vlookup in excel seem to be the Row Filter in Knime

Ah, never thought if it that way. You mean "Include/exclude rows by attribute value" ? Using pattern matching?

Got to think about that one.

Thought I post my solution, in case anyone else can use this -

Don't see how Row filter should work, but I use as before:

GROUP BY

as aggregrate all as list

then I use "JOINER" based on the column for group in GROUP BY.

Finally an ungroup.

If one wants to remove the "List(....)" portion in the headers afterwards, I use  

COLUMN RENAME (REGEX)

search term: ^List\(([a-zA-Z0-9_\s]+)\)$

replace: $1

splitting beforehand and joining afterwards with previous tables isn't necessary, but could help with memory of one has a lot of columns/rows.

 

If you have two tables, an approach would be to use column list loop start, cell replacer and column filter in combination.

1 Like