Hi everyone,
I'm trying to merge some rows of a table based on consecutive time intervals. My table looks something like this:
| Start Date | End Date | Value |
| 01/01/2022 | 02/01/2022 | 10 |
| 02/01/2022 | 04/01/2022 | 10 |
| 04/01/2022 | 05/15/2022 | 10 |
| 05/15/2022 | 06/01/2022 | 20 |
| 06/01/2022 | 08/13/2022 | 20 |
| 08/13/2022 | 09/01/2022 | 20 |
| 09/01/2022 | 10/31/2022 | 20 |
| 10/31/2022 | 12/24/2022 | 10 |
| 12/24/2022 | 01/10/2023 | 10 |
| 01/10/2023 | 03/27/2023 | 10 |
I would like to merge the rows within each block of similar values in the "value" column, so that the result looks like:
| Start Date | End Date | Value |
| 01/01/2022 | 05/15/2022 | 10 |
| 05/15/2022 | 10/31/2022 | 20 |
| 10/31/2022 | 03/27/2023 | 10 |
A simple
GROUPBY(table, [Value], "Start Date Merged", MINX(CURRENTGROUP(), [Start Date]), "End Date Merged", MAXX(CURRENTGROUP(), [End Date]))
doesn't work because the values in "Value" are not unique outside the blocks I want to merge.
Any help with this would be greatly appreciated, as I have been trying to wrap my head around this for quite some time.
Thanks!
