Date range aggregation - Simple .. but complex

Need help
1

Hello guys,

My problem is simple to describe but might be complex to solve, I can’t figure it out, I need your help :slightly_frowning_face:

In the screenshot below, what should I do to go from the left table and obtain the table in the right ? Basically, the left table contains date ranges of events. Events highlighted with the same color are consecutive events and I would like to group them and obtain the table in the right.

As an example, If I have :
Event 1 : from 9am to 10am
Event 2 : from 10am to 11am
I would like to get only 1 event as follow : from 9am to 11am.

image
image1680×459 55.1 KB

2

You want to do it temporarily or permanently? I have tried doing it temporarily (in the frontend etc), and it doesn’t work :slight_smile:

However, if you want to do it permanently (meaning that you want to merge some of the events), it can be done with a recursive backend workflow.

In this recursive backend workflow, you will call workflow on all the events. In the workflow, it will check the first two items:

  • If the end of the first one is the same as start of the second one (MERGE case):
    • update the first item’s end to be the second item’s end, and
    • delete the second one.
    • call the workflow again with all the events again.
  • If the end of the first one is NOT the same as start of the second one (no MERGE case):
    • call the workflow again with the items (minus the first event).

When, the workflow is done, you will have the 3 events from your 6 events.

3

Thanks for your reply Ergin, very interesting approach ! :slight_smile:

The events are not necessary sorted in the table so your algorithm would fail for the situation in the image below: it would not merge the green events. Would it work if I perform a sorting on the fly while using a Search for then applying your steps ?

Thanks for your help !

image
image2005×347 69.7 KB

4

You are right. It will fail if not sorted. I have tried sorting by a date range and it didn’t even show that field. Probably, native Bubble doesn’t support it :slight_smile:

There is a very inefficient sort if the start dates are unique but I wouldn’t recommend that.

5

Ok thank you for your advices !

1 Like