topic Re: Return the count of a string in multiple fields using data expression in ArcGIS Dashboards Questions

Return the count of a string in multiple fields using data expression

dwold — Tue, 27 Feb 2024 17:26:05 GMT

I am trying to build a bar chart illustrating the number of times a string is recorded across multiple (19) fields. I would like to get the total number of times Planning, Organization, Equipment, Training, Exercise, and Unknown appear in POETE area (poete_area) through POETE area 19 (poete_area_19).

This is what I have for my data expression so far but getting tripped up on it:

Any tips would be greatly appreciated.

@jcarlson

@JohannesLindner

Re: Return the count of a string in multiple fields using data expression

JillianStanford — Tue, 27 Feb 2024 17:13:35 GMT

Hi,

I haven't worked with it but would a combination of the Filter and GroupBy functions be helpful?

This sample has an example of the kind of output you're looking for - Aggregate by Day of Week

Re: Return the count of a string in multiple fields using data expression

jcarlson — Tue, 27 Feb 2024 22:26:47 GMT

I bet we could get this with a single GroupBy. We'll use a couple other tricks to keep the expression from getting too long, though.

The crux of it is using a SQL expression to count across fields, like this:

CASE WHEN poete_area = 'Planning' THEN 1 ELSE 0 END +
CASE WHEN poete_area2 = 'Planning' THEN 1 ELSE 0 END +
...
CASE WHEN poete_aera19 = 'Planning' THEN 1 ELSE 0 END

It's a long expression, and you'd have to do it for every single option you want counted up, so the arrays and functions can take care of that for us.

// list of areas; we'll splice it into the field list later var areas = [ "poete_area", "poete_area_2", "poete_area_3", "poete_area_4", "poete_area_5", "poete_area_6", "poete_area_7", "poete_area_8", "poete_area_9", "poete_area_10", "poete_area_11", "poete_area_12", "poete_area_13", "poete_area_14", "poete_area_15", "poete_area_16", "poete_area_17", "poete_area_18", "poete_area_19" ]; var fs = FeatureSetByPortalItem( Portal("https://www.arcgis.com/"), "384158f2ebe34f8ba8b0b39dfe5f5af5", 0, Splice(["Jurisdiction", "core_capability", "target"], areas), false ); // values to sum var values = [ 'Planning', 'Organization', 'Equipment', 'Training', 'Exercise', 'Unknown' ] // function for building SQL string function poeteSQL(p) { // build array of SQL strings per area field var area_strings = []; for (var a in areas) { Push( area_strings, `CASE WHEN ${areas[a]} = '${p}' THEN 1 ELSE 0 END` ) } // give back a single string that sums the areas return Concatenate(area_strings, ' + ') } // array of statistics var stats = [] for (var v in values) { Push( stats, { name: `${values[v]}_count`, expression: poeteSQL(values[v]), statistic: 'SUM' } ) } // group results into single row and sum return GroupBy( fs, {name: 'id', expression: '1'}, stats )

Re: Return the count of a string in multiple fields using data expression

dwold — Wed, 28 Feb 2024 18:39:13 GMT

@jcarlson live saver! Thank you, thank you, thank you!!