Aggregation of Text or Strings?

Question & Answer
, , , ,
1

Hi,

I have an example dataset of weight data which captures users and their weights at most once per day:

|— | — | —|
|Patient_ID | weight | measure_date|
|abc1 | 170.0 | 2025-01-01|
|abc1 | 175.0 | 2025-02-15|
|abc1 | 172.0 | 2025-03-20|

In a QuickSight analysis table, I can use minOver and maxOver functions to capture the first and last weights and dates per patient. I can visualize these in a table:

|— | — | — | —|
|patient_ID | first_weight | last_weight | first_measurement | last_measurement|
|abc1 | 170.0 | 172.0 | 2025-01-01 | 2025-03-20|

Now suppose I have a string field in the original table named “bmi”:
|— | — | —|
|Patient_ID | weight | measure_date | bmi|
|abc1 | 170.0 | 2025-01-01 | normal|
|abc1 | 175.0 | 2025-02-15 | overweight|
|abc1 | 172.0 | 2025-03-20 | overweight|

Is there a way to aggregate the bmi strings in a similar way as numerics and dates?

|— | — | — | —|
|patient_ID | first_weight | last_weight | first_measurement | last_measurement | first_bmi | last_bmi|
|abc1 | 170.0 | 172.0 | 2025-01-01 | 2025-03-20 | normal | overweight|

As it’s currently setup I can capture the first BMI in the range:
ifelse({measure_date} = {parameter_capturing_first_date}, {bmi}, NULL)

I can also create similar for the last BMI:
ifelse({measure_date} = {parameter_capturing_last_date}, {bmi}, NULL)

However, the last value appears as NULL in my table:
|— | — | — | —|
|patient_ID | first_weight | last_weight | first_measurement | last_measurement | first_bmi | last_bmi|
|abc1 | 170.0 | 172.0 | 2025-01-01 | 2025-03-20 | normal | null|

This is because the last_bmi would be captured on a different row under the hood.

So my question is: is there a way to aggregate the last bmi string here in such a way that it “shows up on the same row” as the first_bmi value given the aggregation I’m hoping for?

2

Hi @teuszie ,

Welcome to the community!

You can explore using firstValue and lastValue functions to fetch strings.

Thanks,
Prantika

3

Thanks! I’ve used lastValue as such:

lastValue({bmi}, [{measure_date} ASC], [{patient_ID}])

However, I cannot add it it my table (or any other visualization for that matter). I think it’s because {bmi} is not an aggregated string value – which is ironically a requirement to aggregate the string itself.

image
image718×420 64.1 KB

And even trying to aggregate it says it’s not the correct data type and errors out At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again. , e.g.,
lastValue(min({bmi}), [{measure_date} ASC], [{patient_ID}])

4

Since it is aggregated, you would need to add it as value only, wont be able to use in rows.

5

Hi @teuszie,
It’s been awhile since last communication took place on this thread, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

6

Hi - yeah keep this open. If I can’t use MAX() on a string then this should become a feature request. There’s no answer I’m willing to approve at this time. I had to go back to my SQL and change all string values to a number, perform aggregations on those numeric values, and then add calculated field lookup tables to get them back into a string format.

7

Hi @teuszie,
No problem, I’ll mark this as a feature request to promote visibility to the AWS support team.
If you have any additional notes/comments to add for the feature request; feel free to add below. Otherwise I’ll close out this topic in 3 business days if we do not hear back further.

Thank you