value lookup vs. cell replacer

Community Extensions
1

Hi,

it seems that “cell replacer node” have been replaced by “value lookup node” in KNIME 5.2 (maybe already previous version).

But I don’t see how to rename the appended column in Value Lookup node" (as it was possible with Cell replacer node) avoiding adding Column renamer after.

So far i don’t see the advantage of “Value lookup node” compare to “joiner”. Joiner does same thing as “Value Lookup node”, but much more also!

Thanks for your advices.

Have a nice day

2

Substring / Wildcard / Regex matching has a lot of flexibility in Value Lookup, but other than that I think it was just to re-name the process as something more recognizable to new users coming from Excel. I have seen a ton of forum posts asking how to do vlookups or Value Lookups. Probably just wanted the node to be more intuitive to excel users.

I rarely bother to write something myself when @takbb has likely already written a full length article as a forum post that I can just drop in as a link! :rofl:

2 Likes
3

Hi iCFO,

Thanks for the answer.
I think I explained my point poorly, as this is not concerning the “String matching” option.

So far, in “Value Lookup” we can’t rename the “Append column”, (in other word, its name will be anyway the one coming with “bottom port”). For instance I have two table:

  • Table 1 : one column X
  • table 2 : two column X and “toto”
    I want to append the value from “toto” in table 1 (making the link with both X column) and rename “toto” by “tata”.

This is not a big issue, (not at all), just it was nice to have the possibility to rename the append column directly in the node (in Cell replacer node) and not have to add “Column renamer”, before or after (I try I far as possible to limit the number of nodes in Workflow, which can quickly become huge).

I hope this is clearer.

Have a nice day

2 Likes
4

I get you now, and I very much agree that the ability to name the appended column was useful. I guess we traded that functionality for the ability to append multiple columns from the dictionary table…

1 Like
5

Hi @PierreI , as @iCFO , the Value Lookup node was brought in to provide the equivalent of the Excel VLookup (or more correctly XLookup) functionality which had long been missing from KNIME. Whilst Joiner does indeed provide similar functionality, it is deficient for some very specific use cases. If you are familiar with VLookup and XLookup, you will know that there is a parameter “match_mode” which determines what should happen if no exact match is found (e.g. get next smaller or next larger item). This is functionality that is new in Value Lookup and therefore provides Excel users with what previously would have taken a complex sequence of nodes to achieve. As also mentioned, it is also possible to perform inexact matches

I agree with the sentiment that it would be nice to be able to rename the columns in the node but as has been mentioned, the ability to return multiple columns makes this problematic.

Generally as you mention, the Joiner node provides the functionality for performing large scale joins between tables, and contains the additional capabilities of “outer joins”. This is the equivalent of the SQL “join” function.

The “Value Lookup” provides capabilities of a (normally smaller) “lookup table” which the Joiner can achieve, but with additional functionality akin to the Excel lookup functions, for what to do when the specific exact data is not found.

In KNIME 5.x, when using Modern UI, you can still use the deprecated Cell Replacer by searching for

cell replacer //deprecated

image

It isn’t officially recommended as the aim would be to move away from deprecated nodes, but if it is better for your use case, it can be done. :wink:

2 Likes
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.