How to join 2 tables with greater than value

[DMTUAN]

Hi,
I’m newbie, i want to join 2 table with logical rule as below
Please help me, thank you very much

[HansS]

Hi @DMTUAN and welcome to KNIME Forum

First I thought you want to join the Code from Table A to the nearest value of No in table B.
But then code 100 should match with No=70 (instead of 150).

So what is the logical rule behind this join?

gr, Hans

[DMTUAN]

Hi @HansS
No=20 greater than Code=0 => will be match Rate=1
No=60,70 greater than Code=50 => will be match Rate=2
No=150 greater than Code=100 => will be match Rate=3
It the same logic with vlookup function in excel : vlookup(x,x,x,1)

[bruno29a]

Hi @DMTUAN, in addition to what you mentioned,
No=60,70,150 also greater than Code=0

Similarly:
No=150 also greater than Code=50

[bruno29a]

Hi @DMTUAN , my previous comment was meant to show that the rules you presented were still not fully precised

But I could understand based on your first comment, that you want the highest number of Code where No > Code.

I put something together based on that rule and it looks like this:

The 2 tables (A and B) are the same as yours:
Table A:

Table B:

Here are the results of my workflow:

And here’s my workflow:
How to join 2 tables with greater than value.knwf (13.6 KB)

[DMTUAN]

Thank @bruno29a ,
Very detailed and solved my problem.

[ipazin]

Hello @bruno29a,

you can take advantage of Duplicate Row Filter node to replace GroupBy and Joiner node. Choose No where Code is maximum.

Br,
Ivan

[bruno29a]

Hi @ipazin , you are absolutely right, why didn’t think of that!! Thanks for the pointer.

I changed the workflow to reflect this:

Results (Same as before):

And here’s the new version of the workflow:
How to join 2 tables with greater than value.knwf (11.9 KB)

[Daniel_Weikert]

You could try numeric binner as well but the workflow would probably not be shorter