How can I get average, max and min values per group in the same table?

[Trifacta_Alumni]

To further explain my question, I have a dataset of values based on 10 minutes of a degree for latitude and longitude. I have managed to get an average of values per degree lat/long in a new table, but I would also like to have the smallest and the biggest values per degree lat/long.

Example: I have several rows with degree 156/-10 (long/lat) of which I want only to get 1 row with the average, max and min values. I can do each three of these operations separately, but I can't do it all at the same time so I can export the result.

Thanks for any help you may give me.

[Trifacta_Alumni · Accepted solution]

Hi, Teresa--

Here is the doc on the three functions:

https://docs.trifacta.com/display/SS/AVERAGE+Function

https://docs.trifacta.com/display/SS/MIN+Function

https://docs.trifacta.com/display/SS/MAX+Function

All of these functions support the group by parameter.

The doc suggests using a Pivot transform, which corresponds to the Pivot Table transformation. See https://docs.trifacta.com/display/SS/Pivot+Data.

However, when you pivot the data, you remove the current table and replace it with the pivoted table. So, for your computation, you have to add each function as a comma-separated list in the Values textbox. For example: AVERAGE(myCol), MAX(myCol), MIN(myCol)

If you would like to keep all of your original data, you can use a New Formula transformation instead. Create separate new columns for each computation.

Some doc on that approach: https://docs.trifacta.com/display/SS/Create+Aggregations

Does that help?

Cheers,

-SteveO

[Trifacta_Alumni]

Thank you, I had read the first three you pointed out, but I had not even looked at the pivot table transformation. Again, thank you for pointing me out in the right direction. 🙂