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:
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
Same with a newly to be created “Create Numeric Range”-node
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).