Arcade data expression to return count of values across 5 different columns using GroupBy function

[AaronManuel2]

Hi, I'm working on a data expression for a dashboard bar chart. The dashboard is showing inspections for streetlights in a survey area.

On the point feature being referenced by the dashboard are 5 'observation' columns - 'obs1', 'obs2', etc.

The columns all use the same domain list. The possible values relate to the light status such as 'Always Off' or 'Tree Obstruction'. There are 10 possible values in the domain list. Some points may only have 1, or none of these columns populated. Others may use all 5 of the columns, just depends on the inspection results and how many issues need to be addressed.

What I'm trying to return to the chart is the count of all the unique domain list values, grouped not by the 5 columns, but by the observation values themselves. So that I end up with a bar chart showing, say, 10 counts (or points) of 'Always Off', 15 for 'Tree Obstruction', etc. I could just have 5 separate charts for each observation column but that would obviously not be a very good solution.

I'm not sure how to pass this list to the groupby function, or if I need to approach this in some other way. Some of the examples in this blog post are close but not exactly what I need unless I'm missing something.

Thank You

[JohannesLindner · Accepted solution]

var value_counts = {
    "Always Off": 0,
    "Tree Obstruction": 0,
}
var fields = ["Status1", "Status2", "Status3"]


var p = Portal(...)
var fs = FeaturesetByPortalItem(p, id, layer, fields, false)

for(var f in fs) {
    for(var i in fields) {
        var value = f[fields[i]]
        if(IsEmpty(value)) { continue }
        if(!HasKey(value_count, value)) { value_counts[value] = 0 }
        value_counts[value] = value_counts[value] + 1
    }
}

var out_fs = {
    geometryType: "",
    fields: [
        {name: "Status", type: "esriFieldTypeString"},
        {name: "Count", type: "esriFieldTypeInteger"},
    ],
    features: []
}
for(var value in value_counts) {
    var c = value_counts[value]
    var f = {attributes: {Status: value, Count: c}}
    Push(out_fs.features, f)
}
return Featureset(Text(out_fs))

[AaronManuel2]

Thanks Johannes, this worked perfectly!

[jcarlson]

It is possible to do it all in a single GroupBy if you write the right expression. Look at the following SQL expression:

CASE WHEN obs1 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs2 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs3 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs4 = 'Tree Obstruction' THEN 1 ELSE 0 END +
CASE WHEN obs5 = 'Tree Obstruction' THEN 1 ELSE 0 END

If your point had 4 of the 5 fields marked as "Tree Obstruction", this will return a 4. And if you use this in GroupBy with the statistic SUM, you'll end up with the total of all Tree Obstructions across 5 fields for the whole layer.

The trouble is that you'd need the same 5-line expression for every item in your domain, even though only the domain value is changing. Not that that's a bad thing, but it makes the expression lengthy and annoying.

Now consider a custom function:

function GroupingExpression(domain_value) {
    return `
    CASE WHEN obs1 = '${domain_value}' THEN 1 ELSE 0 END +
    CASE WHEN obs2 = '${domain_value}' THEN 1 ELSE 0 END +
    CASE WHEN obs3 = '${domain_value}' THEN 1 ELSE 0 END +
    CASE WHEN obs4 = '${domain_value}' THEN 1 ELSE 0 END +
    CASE WHEN obs5 = '${domain_value}' THEN 1 ELSE 0 END
    `
}

Now we can just call GroupingExpression('Tree Obstruction') to get the same thing as before. And if we use it to build our GroupBy function, it could look like this:

GroupBy(
    fs,
    {name: 'the_row', expression: 1},
    [
        {name: 'tree_obstruction_count', expression: GroupingExpression('Tree Obstruction'), statistic: 'SUM'},
        {name: 'always_off_count', expression: GroupingExpression('Always Off'), statistic: 'SUM'},
        // and so on
    ]
)

 The result of this should be a single-row FeatureSet with the totals in their own columns, which you can then use to generate a chart.

[AaronManuel2]

Thank you! This is what I was originally looking to do.