How to Join by date range

[hhkim]

Hello.
I have two tables as below.

(Table 1)
|ID|Value|Date|Dist|
|A001|1|2021-01-01|10|
|A001|1|2021-01-01|15|
|A001|2|2021-01-05|10|
|A001|2|2021-01-05|15|
|A001|3|2021-02-07|10|
|A001|3|2021-02-07|15|
|A002|4|2021-02-25|20|
|A002|5|2021-03-04|20|
|A003|6|2021-03-23|30|

(Table 2)
|StartDate|EndDate|Week|
|2020-12-27|2021-01-02|53|
|2021-01-03|2021-01-09|1|
|2021-01-10|2021-01-16|2|
|2021-01-17|2021-01-23|3|
|2021-01-24|2021-01-30|4|
|2021-01-31|2021-02-06|5|
|2021-02-07|2021-02-13|6|
|2021-02-14|2021-02-20|7|
|2021-02-21|2021-02-27|8|
|2021-02-28|2021-03-06|9|
|2021-03-07|2021-03-13|10|
|2021-03-14|2021-03-20|11|
|2021-03-21|2021-03-27|12|
|2021-03-28|2021-04-03|13|

(expected result)
|ID|Value|Date|Dist|StartDate|EndDate|Week|
|A001|1|2021-01-01|10|2020-12-27|2021-01-02|53|
|A001|1|2021-01-01|15|2020-12-27|2021-01-02|53|
|A001|2|2021-01-05|10|2021-01-03|2021-01-09|1|
|A001|2|2021-01-05|15|2021-01-03|2021-01-09|1|
|A001|3|2021-02-07|10|2021-02-07|2021-02-13|6|
|A001|3|2021-02-07|15|2021-02-07|2021-02-13|6|
|A002|4|2021-02-25|20|2021-02-21|2021-02-27|8|
|A002|5|2021-03-04|20|2021-02-28|2021-03-06|9|
|A003|6|2021-03-23|30|2021-03-21|2021-03-27|12|

I want to join by date range and add the week column, but I found that there is no node like this.
I was wondering if there are any way to join by date range.

Thanks.

[mlauber71]

@hhkim welcome to the KNIME forum

You could take a look at this approach

Efficient way of doing a JOIN ... WHERE ...

Well, not a particularly sophisticated idea but you could try and use a local H2 database to join two datasets by time ranges. Depending on the power of your machine and other factors that might work. I tried it with files of different sizes and at least it gives you a result. [image] Until there is a solution in KNIME you could explore further the possibilities of using SQL to make the join more efficient. I took from this older entry: There might be some special limitations when workin…

In addition you could take a look at other threads from the forum:

https://forum.knime.com/search?context=topic&context_id=27474&q=Join%20by%20dates&skip_context=true

[hhkim]

Thank you for your help.

But I’d like to join by range without SQL or cross join if possible.

Thanks.

[bruno29a]

Hi @hhkim , and welcome to the Knime Community.

The only way to do this without SQL would be via a cross join and then apply rule engine to match the range.

This can be quite resource taxing. The most efficient way is via SQL, and if you check what @mlauber71 suggested, you don’t have to set up any db server. It’s all virtual and done via memory.

[hhkim]

Thank you so much.
I’ll check again the example.

[Daniel_Weikert]

I think this can be done with a dictionary binner node
br

[mlauber71]

@Daniel_Weikert maybe you could provide an example. Could be an interesting addition.