Joining two tables based on date ranges

Hi there,

I have two tables, one table containing information on when a particular car was on the road and another table which is a scheduling table showing when certain cars are to be used and by which team.

I need to join them together in Knime (the two tables will draw from databases and are very very big, we are talking hundreds of thousands of records).

I have an example of what they look like. Basically I want to join the two tables so I can know which team was driving the car because the cars can be shared between different teams:

3-12-2021 6-53-34 PM

3-12-2021 6-53-57 PM

Please ignore if the times and dates don’t make sense, I quickly threw this together, I just need help with the logic.

I’ve looked up many examples that make use of Binner (Dictionary) etc but I couldn’t find any help on matching it based on the Car # and then the time the car was driving (Table 1) against the scheduled times (Table 2).

Any help is very much appreciated please.


Hi @ViTLe

Would it be possible for you to share here in CSV or XLS format at least a few rows of the two tables you need to join? The more the better but a few dozens would already help us to work with and suggest possible solutions.
Sharing a bit of data will help people to provide you with an appropriate solution.

Thanks & regards,


Hi @aworker,

Thank you for volunteering to help.

Please find attached some sample data. The two tables are on two different worksheets

Sample.xlsx (13.4 KB)

Each team has multiple members so you can have the same team driving different cars on during the same schedule.

It seems like a very simple problem but I can only make it work if each schedule range is unique and only one car per schedule range but in my car, you can have different cars driving during the same schedule, which is what I am stuck on.

Kind regards,

Hi @ViTLe

My pleasure to help :slight_smile:

Joining tables by -date ranges- is not something trivial at a first glance so I understand the difficulty to get to a solution :wink:

Please find below a possible solution. The -Sorter- node is not stricty needed appart from easing the understanding of the results. The Chunk Loop is neither strictly needed but it will help you to optimize the use of your computer memory if you have huge tables as you said. Otherwise, it could be done without it. I let you to experiment with it :wink:

20211204 Pikairos Joining two tables based on date ranges.knwf (317.8 KB)

Hope it helps




Just to add my thoughts here.
I could think of two options here. Either write the data to database and use that as it allows joining ranges or binning the data.


Hi @ViTLe,

if you know any sql and have python installed, you can use the following component to achieve this…

A demo workflow is available here


@ViTLe indeed on option would be to use a H2 database to realise a BETWEEN join.


Hi @aworker thank you so much, I will play around with this and see how it works and if it gives me the results I am looking for, I might come back to you if I hit a roadblock.

1 Like

Hi @takbb I know very basic SQL and I don’t have python installed so this might be an option for when I am more experienced, at the moment, I am extremely novice and was given a complex task with very short turnaround (I am stressed! haha)


Hi @ViTLe

My pleasure and thanks for your kind words. I’ll be happy to continue helping you on this if the workflow needs more work or improvements.

Amazingly here it is still Friday but at your place in Australia it is already Saturday lol. So have a nice day and looking forward to getting your feedback :slight_smile:

Best wishes


Hi @Daniel_Weikert I actually saw someone using the binner (dictionary) node to try and work this but I could not replicate the logic to get it to work for me. I really new to Knime so a lot of these I just can’t get it to work. KNIME_project_binning.knwf (1.1 KB)
Sample.xlsx (13.4 KB)

This is as far as I could go and I mapped up the output and it doesn’t give me what I need. I feel like I am missing a lot of parts but I don’t know how to get it right.

What suggestion would you give?

@aworker haha yes, it is a Saturday early morning but I am pulling in extra hours to get this done as they gave something very complicated to the least experienced person and I am a tad stressed.

No worries at all @ViTLe ! You’ll certainly get it done in time with the help of all the volunteers at the KNIME forum. Even during the weekend lol :wink: !

Best wishes

@aworker I just tried the workflow as you had provided but the Joiner (Labs) node is giving me an empty table but I didn’t change anything from what you provided. I’m not sure why, do you have any suggestion?

@ViTLe do you mean exactly as I upload it ? It is strange because it comes with the data, already run and it didn’t return an empty table. Have you changed the data in the meanwhile ?

Was it run or reset when you opened it ?

Sorry I’m answering from my mobile phone without access to my computer so I cannot check by myself if I uploaded it run or reset but I believe it was run.

@aworker when I loaded your workflow, I got a message to say that my version does not support a workflow made by a newer release of Knime but I loaded it anyway, I didn’t think that might cause the issue though! But otherwise, all the nodes run successfully except when it gets to the Joiner (Lab) node then it gives me an empty table, it still runs but just returns no data.

I will upgrade my Knime and try again?

@ViTLe I understand better the issue now. Indeed KNIME is very picky with versions. If you can update without too much hassle then try it. Otherwise by heart I believe to remember the configuration. Before updating, please replace the joiner (labs) by the old joiner available in your current KNIME version and use the “car” column as key for an inner joining. Then keep all columns but the car from the right table. It should work this way. Let me know please if this amends the problem.

1 Like

Hi @aworker I just spent the last hour using your workflow as a guide against the actual real data (pulling from the database from hundreds of thousands of records) and spot checked about half a dozen and it seems to be working correctly!

Thank you SO much for helping me in your spare time. Please extend gratitude to your team (if you worked with a team) for this solution. I have a Knime hero in FRANCE, what a wonderful world!


Hi @ViTLe

Thanks a lot for your SO kind message and I’m really glad the workflow is working correctly :smiley: :+1:!!

There is no people working with me but certainly a great team around all of us at the KNIME forum as you could see with great volunteers like in this case @Daniel_Weikert, @mlauber71 and @takbb willing to help with different solutions ! Indeed KNIME is a wonderful world :wink: !

May be you will have the opportunity to meet the KNIME volunteers at the next KNIME Release Data Talks event on 9th December:

This is a virtual alive meeting where you can meet in direct other KNIME fans and members and discuss about your KNIME experience. It is also an opportunity to ask questions.

Have a nice spring weekend in Australia !




@aworker Thanks for the heads-up. I signed up for the free ticket in case I am able to make it, time zones are a killer though.