Vlookup (Excel) type of function/workflow?

KNIME Analytics Platform
1

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.

 

2

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

3

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

Got to think about that one.

4

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.

 

5

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

1 Like