How do I match to the lower value?

[CarishmaM]

I am trying to compare to documents in which which:

Document one has:

54654
87684
56468
89987
65498

Document two contains two rows to perform a vlookup
54650 1111
87642 1565
55462 2135
84586 5646
65480 9999

I want Knime to Match Document 1 to Document 2 by using the next lower value. So,

54654 would match or become 54650
87684 would match or become 87642
56468 would match or become 55462
89987 would match or become 87684
65498 would match or become 65480

I tried using binary dictionary but this would pull the lowest value for the all of the cells.

[aworker]

Hi @CarishmaM

I have a question that sounds important to me before answering your question and may be providing an optimized solution:

Could it happen that two numbers in the initial column could match the same new lower value? Or otherwise, this is never the case ? Depending on this, the solution can be definitely different.

Hope this helps.

Thanks & regards,

Ael

[CarishmaM]

Hi,

Yes, this is very much possible.

[aworker]

Hi

Are 2nd column values always unique ?

1111
1565
2135
5646
9999

Another question: I do not understand why

@CarishmaM

89987 would match or become 87684

Just for this row, the value is replaced by the nearest lower value of the same table ? Is this correct ?

Best

Ael

[CarishmaM]

Hi,

The 2nd Column values linked to the first column. This is used to perform a vlookup.

[CarishmaM]

Allow the numbers from the first document match the equal or the nearest lower value

[aworker]

@CarishmaM

89987 would match or become 87684

Then the match above is not correct. The right match should be

89987 would match or become 87642

as follows:

Could you please confirm ?

[CarishmaM]

Yes, that is correct

[aworker]

In this case, the following workflow may be a possible solution:

20210930 Pikairos How do I match to the lower value.knwf (66.6 KB)

Hope this helps.

Best

Ael

[CarishmaM]

Hi Ael,

I noticed that my 2 Table has more lines that the first table. So instead of aligning the way yours day, it looks like this:

Do you know if there is a way to have the data begin at the beginning versus the end?

[aworker]

Hi @CarishmaM

Sorry I cannot understand where the problem comes from without inspecting the workflow with the new data. Can you upload here the workflow with the new data to have a look at it ?

[morpheus]

Hi @CarishmaM,
it seems you opened for the same issue a second topic (VLookup Formula).

You’ve got an example to work on. The only thing you need to do is to think about your data. For e.g. are your data nummeric. If not it doesn’t work. Second you have only to exchange the datetime diefference node with a math formula node.

BR

[CarishmaM]

Hi @morpheus,

I was able to use the example @aworker provided with a few modifications. Thanks for the additional information.

[aworker]

Hi @CarishmaM

Good to know and thanks for this feedback. You have just checked your own answer as the solution. If the workflow I posted was of help and solved your problem, could you please validate the workflow as the solution ? There is a next to it for that.

Thanks & regards,

Ael