Forum Academy Marketplace Showcase Pricing Features

Group by day and display last value of that day

Need help
#1

Hi everyone,

The title of my topic is not really easy to understand so let me try to explain with a simple example.
I have the following data from an API call:

ID, execution_date, value
#A, 2023-02-01 8:00am, 1000
#A, 2023-02-01 18:00am, 900
#A, 2023-02-01 19:00am, 1200
#A, 2023-02-03 12:00am, 1300

The result I need is:
#A, 2023-02-01, 1200
#A, 2023-02-02, 1200
#A, 2023-02-03, 1300

So, first I need to group by day and find the last value of the day (with the max(execution_date)).

#A, 2023-02-01, 1200
#A, 2023-02-03, 1300

Then I need to expand to all days between the min and max date and fill empty values with the previous value.

#A, 2023-02-01, 1200
#A, 2023-02-02, 1200
#A, 2023-02-03, 1300

Do you have any idea how I could handle this? I started with a group by ID and day(execution_date) with a max(execution_date) aggregation but I don’t know how to merge this list with the raw data from the API call.

Thank you all for your help

#2

One approach is to group by a bucket (on the date day) and aggregate by max (on the value).

https://manual.bubble.io/core-resources/data/operations-and-comparisons … skim down to the word bucket …

You might need to alter the DB to make it feasible.

I’ve a working example here https://knowcode-tech.bubbleapps.io/version-test/group_by_having_max

but what I am trying to do is group by a text field and find the max value in the grouped text field. (so I add a proxy bucket number for the text field for me to bucket on)

… Bubble doesn’t have a “having” operator.

#3

Hey, thanks for you reply.

The group by day and max(value) will not work because the last value is not always the maximum value of the day (see me example).

I tried group by day and max(execution_date) to catch the execution_date of the last value of the day. Now I need to “merge” this list with the original data with ID = ID_grouped AND execution_date = max_execution date from the grouped list.

So easy to perform with SQL that’s frustrating… I’m stuck.

#4

ok, then max on the date then :slight_smile: