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:
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).
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.
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.
Joining tables by -date ranges- is not something trivial at a first glance so I understand the difficulty to get to a solution
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
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 @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.
@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?
@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.
@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.
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!
Thanks a lot for your SO kind message and I’m really glad the workflow is working correctly !!
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 !
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.