Good day Pau.
You can make a join that relies on a date on one table being between two dates in the second. Quick example from SuperStore:
But a question, is there ever more than process being executed between the start and end time of another? Because if a start/stop time can include more one executed process, then you're not going to get a true join.
Does this help?
Hey Daniel, thanks for your answer.
The thing is, each execution (with a starting and ending date) can complete several processes (each with their hour of execution). So I would like to have a join where I could know how many processes have been completed on each execution (by looking at how many records from the first table have their hour of execution between the starting and ending date in the second table). It is clear to me that the resulting table would have duplicate values of the starting and ending date, as several processes will be completed under the same execution, so that's not a problem at all, it is what I expect.
About the example you provided, I already tried that before writing my first post by trying to apply both join conditions (Hour of execution (table 1) > Start Date (table 2) and Hour of execution (table 1) < End Date (table 2)), but the resulting table I got only had nulls everywhere. I don't have a certain explanation for that, but I guess Tableau can't work as a typical programming language would do, looping through both tables to join the appropriate values.
Good day Pau.
This sort of joining should work. Ideally, all fields being compared are datetimes. Is the field "hour of execution" a time? Or a datetime? Or something else? And are start/end dates datetimes? And lastly, would it be possible to see a screenshot or sample data?
It does work, I was wrong on the join type. I tried right join instead of left join and it worked. Also, I've been able to get to the same result with a SQL query.
Thanks for your help!