Hi, I have a problem.
I want to check if for the same ID, there is overlapping Start Date and End Date (the Bolded Row) in table 1. I want it to fill the data of that bolded row to those that are applicable to result in table 2.
Table 1
ID | Start Date | End Date | Period | A1 | A2 | B1 | B2 | C | D1 | D2 | E1 | E1 |
111 | 4/1/2020 | 9/30/2020 | 1 | 0.7 | 0.7 | |||||||
111 | 10/1/2020 | 12/31/2020 | 1 | 0.7 | 0.7 | |||||||
111 | 10/1/2020 | 9/30/2021 | 1 | 0.3 | 0.3 | |||||||
111 | 1/1/2021 | 9/30/2021 | 1 | 0.7 | 0.7 |
Table 2 (Desired Result)
ID | Start Date | End Date | Period | A1 | A2 | B1 | B2 | C | D1 | D2 | E1 | E1 |
111 | 4/1/2020 | 9/30/2020 | 1 | 0.7 | 0.7 | |||||||
111 | 10/1/2020 | 12/31/2020 | 1 | 0.7 | 0.7 | 0.3 | 0.3 | |||||
111 | 1/1/2021 | 9/30/2021 | 1 | 0.7 | 0.7 | 0.3 | 0.3 |
How can I do that? Advanced thanks!
Hi
I think this is similar problem, basically join both table with ID and then use formula to check if one date range is overlapping with other
Re: Date Schedule Wrangling/Manipulation - Alteryx Community
Regards
Arundhuti
Hi, Table 2 is the result that i want so i cant join anything.
Hi @jt98,
This is a pretty lengthy solution (I'm sure someone can come up with a more efficient one), but it returns the desired output and is relatively dynamic. It uses append fields to create all pairs, so it'll take significant computing time/power for large datasets, which is a potential drawback. I hope it helps!
If you closely look at the original solution link, it is joining same table twice. Same thing can be done here