-
1. Re: Can I join two tables where a date in the first one is between two dates in the second one?
Daniel Stanish Nov 27, 2018 9:52 AM (in response to Pau Tur)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?
-Dan
-
2. Re: Can I join two tables where a date in the first one is between two dates in the second one?
Pau Tur Nov 27, 2018 11:35 PM (in response to Daniel Stanish)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.
-
3. Re: Can I join two tables where a date in the first one is between two dates in the second one?
Daniel Stanish Nov 28, 2018 4:11 AM (in response to Pau Tur)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?
Best regards,
-Dan
-
4. Re: Can I join two tables where a date in the first one is between two dates in the second one?
Pau Tur Nov 28, 2018 4:51 AM (in response to Daniel Stanish)Dear Daniel,
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!