JOIN where Primary key value in a RANGE of Secondary key values

In SQL, a JOIN can be made using the following argument:

[…]
ON t1.Date >= t2.StartDate and t1.Date < t2.EndDate
[…]

So, the Date in the primary table should be in the given range in the secondary table.

In KNIME, the only way to do the same would be to use a Cross Joiner in order to create a maximum Carthesian product and then to filter out all the crap.

This, of course, is a big waste of resources and it would be a very powerful functionality for KNIME to have.

Could the Joiner node be expanded with this functionality?

(Note: apart of a range, simple >, >=, < and <= should of course also work)

Possible alternatives to the expansion of the Joiner node functionality:

  1. expand the functionality of the “Create Date&Time Range”-node, where the Starting Point and/or End Points could be not only a hardcoded value, but also a field value
  2. Same with a newly to be created “Create Numeric Range”-node

Hi @sambadancer.

Take a look at this component. Is this what you need?

Br

Hi @hmfa,
Thanks for your reply!
I’m afraid not, unless I don’t understand the Join Contains node: that node seems to match a value in the secondary table to a substring of a value in the primary table.
That would not work with either date or number ranges.

Example: My primary table contains loans handed out on a certain date (for example October 23rd, 2024). I want to match that date to a date range in my secondary table; There, I have a line that says that between October 1st, 2024 and December 31st, 2024 (the 4th quarter), the tariff was 9,23%.
In SQL, this would be rather easy, but in KNIME I would have to create a HUGE carthesian product between the Primary and Secondary tables. Only in that result, I can check whether my date falls within that range, probably resulting in 99.9% of all records being thrown away. That is, of course, very inefficient (and slow).

I understand it is a needed inprovement.
The joinner node doesn’t have a where clause.

For now, have you explored the comunity join components like those of @takbb for a solution?

Br

1 Like

Thanks, @hmfa, I will certainly have a look at this!