Join tables based on a date range

KNIME Analytics Platform
1

 

I'm looking for a way to take table #1 that has min and max timestamp values and join it to  table #2  based on whether the time stamp in table #2 falls withing the min and max values in table #1. Any suggestions (or a better way to do it)?

2

Hi Jontimko,

 

I realized this using a combination of the time difference nodes (to get real values out of the date/time cells) and a loop over only one column, for selecting the min and max value.

 

I will just upload the flow here, so you can reproduce it with your own data. However, you need the modular data generation nodes for opening the flow.

 

Best,

Iris

3

We are working on a node for KNIME v2.4 that categorizes a value column according to a rule table with lower and upper bounds. This should work equally well for date/time values. Let me know if you want a preview.

4

If you have something to play with now I'd be glad to try i out!

5

Hi all,

I'm sorry to resurrect this very old post. Has there been any update on this topic?

Thanks

6

Have a look at "Binner (Dictionary)".

7

I’d love to see an early version of this node. I’m running 4.2.1 but if there is a node that will allow a join with ranges that would solve many problems I have right now.

8

It would be nice to have but with current node set you need to use cross joiner and then rule based filter. It would be logical to add rule based filter as a part of cross joiner but KNIME developers understanding is to have nodes with separate functions not a complex more universal tools.

9

Hello!

@scottrichardmcleod_yahoo.com I always used either loop either @izaychik63 approach but couldn’t Binner (Dictionary) be used actually?

Br,
Ivan

10

Binner Dictionary does work and thats my fallback. But its also a little slow. Especially with milti-million row tables. I’m hoping to see the new joiner node that incorporates a BETWEEN style that would include numbers, dates and string will have some optimization built-in for the join.

11

Hello @scottrichardmcleod_yahoo.com,

actually there is already a ticket related to your request so added +1 on it. (Internal reference: AP-10692)

Br,
Ivan