Show minimum value in a range of data

[BenBlowers2]

Hello all,

I am trying to summarise some data from multiple borehole surveys. Each borehole is visited up to 6 times, and each time a new record is submitted via Survey123. The depth to water is recorded on each visit. I would like a simple table, or indicator that shows this in a dashboard, so it looks something like this:

Depth to Water
Borehole	Visit1 (R1)	Visit2 (R2)	Visit3 (R3)	Visit4 (R4)	Visit5 (R5)	Visit6 (R6)	Min	Max
BH101	2.6	2.4	3.2	2.5	3.2	2.9	2.4	3.2
BH102	1.8	1.6	1.6	1.3	1.9	1.5	1.3	1.8

At the moment I can only get the Dashboard to show each visit as a new line in either the table or list. Is it possible to show this data summarised, so that just 1 line exists for each borehole? And then show the different depths recorded, and use an expression to highlight/pull out the min/max?

Thank you,

Ben

[JohannesLindner]

You can use the GroupBy function for that:

var test_data = {
    geometryType: "",
    fields: [
        {name: "Borehole", type: "esriFieldTypeString"},
        {name: "Visit", type: "esriFieldTypeString"},
        {name: "WaterLevel", type: "esriFieldTypeDouble"},
        ],
    features: [
        {attributes: {Borehole: "BH101", Visit: "R1", WaterLevel: 2.6}},
        {attributes: {Borehole: "BH101", Visit: "R2", WaterLevel: 2.4}},
        {attributes: {Borehole: "BH101", Visit: "R3", WaterLevel: 3.2}},
        {attributes: {Borehole: "BH101", Visit: "R4", WaterLevel: 2.5}},
        {attributes: {Borehole: "BH101", Visit: "R5", WaterLevel: 3.2}},
        {attributes: {Borehole: "BH101", Visit: "R6", WaterLevel: 2.9}},
        {attributes: {Borehole: "BH102", Visit: "R1", WaterLevel: 1.8}},
        {attributes: {Borehole: "BH102", Visit: "R2", WaterLevel: 1.6}},
        {attributes: {Borehole: "BH102", Visit: "R3", WaterLevel: 1.6}},
        {attributes: {Borehole: "BH102", Visit: "R4", WaterLevel: 1.3}},
        {attributes: {Borehole: "BH102", Visit: "R5", WaterLevel: 1.9}},
        {attributes: {Borehole: "BH102", Visit: "R6", WaterLevel: 1.5}},
        ]
}
var boreholes = FeatureSet(Text(test_data))
//return boreholes

var range_statistics = [
    {
        name: "Min",
        expression: "WaterLevel",
        statistic: "MIN"
    },
    {
        name: "Max",
        expression: "WaterLevel",
        statistic: "MAX"
    }
    ]
var boreholes_with_range = GroupBy(boreholes, "Borehole", range_statistics)
return boreholes_with_range