Groupby

[SRSSpatial]

Hi guys,

I have a table that I want to use the groupby function in 2 different ways, then join it back to calculate the percentages to display on ArcGIS dashboard. I am very new to ArcGIS Arcade so this has been a major struggle. Can someone please provide some guidance? 

What I have got so far:

//Grab data

var portal = Portal('https://www.arcgis.com/');

var fs = FeatureSetByPortalItem(portal,'id',0,['LGA','Activity']);

//Table  1

var LGA_Activity_Count = GroupBy(fs,['LGA','Activity'],[

 {name: 'Local_Activity_Count', expression: 'Activity', statistic: 'Count'},]);

//Table 2 

var LGA_Activity_Count = GroupBy(fs,['Activity'],[

 {name: 'State_Activity_Count', expression: 'Activity', statistic: 'Count'},]);

//Join Table 

//(Table 1 divide by table 2) * 100 to work out the percentages

[JohannesLindner]

// function to load Featureset into RAM (for performance)
// community.esri.com/t5/arcgis-online-blog/improving-expression-performance-a-custom-function/ba-p/1288785
function Memorize(fs) {var temp_dict = {fields: Schema(fs)['fields'], geometryType: '', features: []}; for (var f in fs) {var attrs = {}; for (var attr in f) {attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr]);} Push(temp_dict['features'], {attributes: attrs});} return FeatureSet(Text(temp_dict))}


// load Featureset
var fs = Memorize(FeaturesetByPortalItem(Portal("https://www.arcgis.com"), "b0d335151aad48a5883326b9aed69cdd", 0, ["LGA", "Activity"], false))

// GroupBy county and activity fields
var local_activities = GroupBy(fs, ["LGA", "Activity"], [{name: "LocalActivityCount", expression: "1", statistic: "COUNT"}])
// GroupBy activity field
var state_activities = GroupBy(fs, ["Activity"], [{name: "StateActivityCount", expression: "1", statistic: "COUNT"}])

// create empty output featureset ( add percentage field to local_activities)
var out_fs = {
  geometryType: "",
  fields: Splice(
            Schema(local_activities).fields,
            [{name:"PercentageOfTotal", type: "esriFieldTypeDouble"}]
            ),
  features: []
}

// iterate through the local activities
for(var f_local in local_activities) {
  // find the corresponding feature in the state activities
  var a = f_local.Activity
  var f_state = First(Filter(state_activities, "Activity = @a"))
  // copy the local activity attributes
  var att = Dictionary(Text(f_local)).attributes
  // calculate and append the percentage
  att.PercentageOfTotal = f_local.LocalActivityCount / f_state.StateActivityCount * 100
  // add the new feature to the output fs
  Push(out_fs.features, {attributes: att})
}

return Featureset(Text(out_fs))

[SRSSpatial]

Hi Johannes, 

Thank you so much for taking the time to answer my question!

Unfortunately I've received an error message (image below). I am thinking this might have something to do with the "Activity" column that's sitting in the back-end as there are few records showing null/blank value. Is there any way to fix this? 

Thanks 

Calvin

[JohannesLindner]

Yes, it is because of null values. To fix it, you can insert this after line 7 to ignore features with null values in either field:

fs = Filter(fs, "LGA IS NOT NULL AND Activity IS NOT NULL")

[SRSSpatial]

Thank you so much Johannes. This is exactly what I am chasing after :). 

Any chance you know why the "StateActivityCount" column isn't displaying? 

Cheers