Fill one table with values from another table based on conditions

Hi everybody,

i am stucked on a rather small problem (imo) but i have no idea how to solve it.
I have table A and table B.

table A looks like this

Column date | Column condition_info | Column C | Column_ID
2023-06-30 | West | Info1 | 1
2023-06-30 | East | Info1 | 2
2023-06-30 | South | Info1 | 3
2023-06-30 | North | Info1 | 4

and table B looks like this

Column date | Column condition_info | Column Sales | Column_ID
2023-06-30 | West | Info2 | ?
2023-06-30 | East | Info3 | ?
2023-06-30 | South | Info4 | ?
2023-06-30 | North | Info5 | ?

I want now to fill the Column_ID in table B with the numbers from table A based on the date and the conditional_info. And only the numbers, not the whole table A.
Joiner node and value lookup node didnt worked…
In general this is a simple if-statement, but there are to much values to use the rule engine node.
Is there a workaround?

Thank you.

Hi @Stbin , are all the rows of Column_ID in table B always empty?

If so, you would use a joiner node to join table A to table B using the columns you have specified in the join, and configure it to not return Column_ID from table B, and return only Column_ID from table A. If you connect Table B to the upper port and Table A to the lower port, you’d want to return Matching Rows and Left Unmatched Rows.

If on the other hand, Table B contained some values in Column_ID which you might want to keep, you could do the join as before but return both Column_ID from Table B and Table A. You could then use a Column Merger node, to update the Column_ID using values from Column_ID (right).

In this workflow, I’ve used a component from the hub to easily include your sample data:

Populate one table from another.knwf (141.3 KB)

2 Likes

Thanks for your answer and the example. I think i have a different problem beside that.
When i do everything as you said, the joiner fills only the first matching row, every other row gets ignored.
I played with your example and when i add more rows to your table B it still works as intended…

Do you have maybe a solution for this too?

Mine:

Yours:
knime ex2

Hi @Stbin, there aren’t any settings in the joiner that would make it “join only on first match”, so this sounds like something specific in your data. The joiner will only match on exact matches. Is it possible you have spaces or other characters in your data that might make it appear they are the same, but in fact are different?

Or are you including a column in the join that means it only matches that one row? If you remove the “first row” that currently matches from your data prior to the joiner (e.g. by temporarily putting in a row filter and removing that row from the data), does the joiner then match anything?

Did you do a “row level” join where you included a row ID or Row number as a join criteria?

I like to split Left, Right and Center join to help see what is happening better if there is an unexpected result.

1 Like

Hi,

thanks for the suggestions. There were indeed whitespaces in my data…
Now everything works.

2 Likes

Hi @Stbin , glad to hear you found the problem and got it working. And thanks for marking the solution.

1 Like

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