How to do vlookup for certain cells

Hello
My table contains some cells with the value “U”
Is there a way to do vlookup to replace ONLY the cells with the “U” using a reference table.
When I use joiner im forced to change all cells even the ones that dont have a “U”

I haven’t used it in a while, but I believe that the “Rule Engine Dictionary” node provides a good solution for this situation.

@iCFO can u elaborate how pls

@lamasewidan, You would need to share a workflow that has some sample data in the 2 tables and what Joining rules are required in order to know for sure, but here is a quick workflow to show you what it can do for your use case.

rule_engine_dictionary.knwf (28.6 KB)

2 Likes

You could also do it by using a row splitter to get to the rows with just the “U” values in the target column, then use the a row ID column from the filtered table to apply the same row filter to the second table using the “reference row splitter”, and join the filtered tables, then concatenate back in the rows that failed the “U” filter on table 1.


This is the output.

All the cells with the value “unchanged” have a value that needs to be restored. These values are in another table. (both tables are identical, have same row IDs and and columns ) I want to use the row ID and column name to lookup the value of “unchanged” cells and put it back.

@iCFO forgot to tag you

With that many columns, it might be a good approach to pivot them 1st… Can you upload a workflow with some data from the tables in it?

2 Likes

@iCFO


I dont know how to upload a workflow but here you go
The upper table node has cells with values “unchanged”
The lower table node acts as a dictionary to restore the values of these cells

Just go to file and export workflow in KNIME. Then upload the saved location to the forum.

Try to keep the column names the same as your use case.

@iCFO I dont understand what you mean by “use case” but here you go
Forum.knwf (6.2 KB)

1 Like

“Use case” is just a shorthand way of saying the specific problem that you are solving. I will take a look at this later tonight if I can, or in the morning.

How does this look. It should be easy to adjust for any number of columns.

vlookup_pivot_join.knwf (74.2 KB)

1 Like

Here is a similar pivot approach using the Rule Engine with a row by row count in the formula.

rule_engine_dictionary 1.knwf (78.8 KB)

1 Like

@iCFO the counter generation node is missing from my knime version. can u suggest another node to replaced it

vlookup_pivot_join.knwf (74.5 KB)

You can use the Math node with the formula ROWINDEX+1, create a new column and then select convert to int. This was how I did it before the Counter Generation node existed. I added it to one of the solutions for you.

2 Likes

@iCFO
Done. Thank you so much!

1 Like

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