Hello,
My data is organized with a unique code in the first column and then a date grid as below.
Code | 2023-03-09 | 2023-03-16 | 2023-03-23 | 2023-03-30 | 2023-04-06 | 2023-04-13 | 2023-04-20 | 2023-04-27 | 2023-05-04 |
123 | x | x | x | x | x | ||||
456 | x | x | x | x | x | x | |||
789 | x | x | x | ||||||
101 | x | x | x | x | x | x | |||
102 | x | x | x | ||||||
103 | x | x | x | x | x | x | x |
I would like to transform the data to give me the begin and end dates of each wave/iteration. There is not a need for more than 3 waves. I would like the data to look like this once complete.
Code | Wave 1 Start | Wave 2 End | Wave 2 Start | Wave 2 End | Wave 3 Start | Wave 3 End |
123 | 2023-03-09 | 2023-03-23 | 2023-04-06 | 2023-04-13 | ||
456 | 2023-03-16 | 2023-03-30 | 2023-04-20 | 2023-05-04 | ||
789 | 2023-03-30 | 2023-04-13 | ||||
101 | 2023-03-09 | 2023-03-16 | 2023-03-30 | 2023-04-06 | 2023-04-20 | 2023-04-27 |
102 | 2023-03-30 | 2023-04-13 | ||||
103 | 2023-03-09 | 2023-04-06 | 2023-04-20 | 2023-04-27 |
Thank you for any assistance!
hey @RBoyd
Would you be able to explain the concept of the "Waves" in your desired output.
I am not sure I follow how these are being derived.
Absolutely. The X under each date signifies that that code is active for that week. So a wave would consist of consecutive weeks in which an X is present. For instance, in the initial table i posted code 123 would have 2 waves as there is 2 strings of consecutive x's present.