Dashboard Data Expression to Combine Multiple Feature Sets and GroupBy Common Field

[KathrynWesson]

Hi everyone! I have another question regarding one of my data expressions in ArcGIS Dashboards.

I have four layers published to a feature service, which I'm trying to combine by their common field 'team name' and use in a Pie Chart to show total length surveyed by each team. Ideally, the expression will return a feature set that I can use in the Pie Chart function like this:

I think I have to use the GroupBy() function somewhere in my data expression, and maybe also add another line to my dictionary, but I'm not sure how I would do that/incorporate all of that with more than one feature set. Here is my data expression so far:

var sql = "EXEMPT_COD IS NULL Or EXEMPT_COD = 2 Or EXEMPT_COD = 4"

var fs_flexible = FeatureSetByPortalItem(Portal('https://xxxxx.xx.gov/portal'), 'xxx', 0, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_flexible = Sum(Filter(fs_flexible, sql), 'Length')

var fs_rigid = FeatureSetByPortalItem(Portal('https://xxxxx.xx.gov/portal'), 'xxx', 1, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_rigid = Sum(Filter(fs_rigid, sql), 'Length')

var fs_concrete = FeatureSetByPortalItem(Portal('https://xxxxx.xx.gov/portal'), 'xxx', 2, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_concrete = Sum(Filter(fs_concrete, sql), 'Length')

var fs_gravel = FeatureSetByPortalItem(Portal('https://xxxxx.xx.gov/portal'), 'xxx', 3, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_gravel = Sum(Filter(fs_gravel, sql), 'Length')

var sumDict = {
    'fields': [{'name':'Length_Miles', 'type':'esriFieldTypeDouble'}],
    'geometryType': '',
    'features':
    [{'attributes':
     {'Length_Miles': GroupBy((sum_flexible + sum_rigid + sum_concrete + sum_gravel), ['SURV_CREW'], [ { name: 'lengthbyteamname', expression: 'sumlengthbyteamname', statistic: 'SUM' } ])
     }}]};

return FeatureSet(Text(sumDict));

Do I need to 'flip' my order of operations so that the GroupBy() function occurs up in lines 4, 7, 10, and 13 and the Sum() function from those lines occurs down in my dictionary attributes? If so, how would I alter my script to achieve this? Additionally, how can I change it to get the results I want?

Thanks for any help!

[Soobin-Choi · Accepted solution]

Hi Kathryn, you may need to swap your operations and aggregate your data first before combining the results into one feature set.

Refer to this sample here: https://github.com/Esri/arcade-expressions/blob/master/dashboard_data/CombineMultipleLayers(SerialCh...

In your case, the expression would look something like this: 

var sql = '<INSERT SQL STATEMENT>'

var fs_flexible = FeatureSetByPortalItem(...)
var fs_rigid = FeatureSetByPortalItem(...)
var fs_concrete = FeatureSetByPortalItem(...)
var fs_gravel = FeatureSetByPortalItem(...)

var sum_flexible = Sum(Filter(fs_flexible, sql), 'Length')
var sum_rigid = Sum(Filter(fs_rigid, sql), 'Length')
var sum_concrete = Sum(Filter(fs_concrete, sql), 'Length')
var sum_gravel = Sum(Filter(fS_gravel, sql), 'Length')

var features = []

var feat_flexible = {
attributes: {
<INSERT ATTRIBUTE> 
}
}
Push(features, feat_flexible)

var feat_rigid = {
attributes: {
<INSERT ATTRIBUTE> 
}
}
Push(features, feat_rigid)

var feat_concrete= {
attributes: {
<INSERT ATTRIUTE> 
}
}
Push(features, feat_concrete)

var feat_gravel = {
attributes: {
<INSERT ATTRIBUTE> 
}
}
Push(features, feat_gravel)

var sumDict = {
fields: [
<INSERT FIELDS>
],
geometryType: '',
features: features
}

return FeatureSet(Text(sumDict))

[KathrynWesson]

Thank you very much! That put me on the right path. I was able to match my expression to your example (the GitHub link also helped) and get what I needed. I've pasted my working expression below, in case anyone in the same situation stumbles across this post.

var sql = "EXEMPT_COD IS NULL Or EXEMPT_COD = 2 Or EXEMPT_COD = 4"
var portal = Portal('https://xxxxx.xx.gov/portal');
// Create a FeatureSet for each pavement type Feature Layer. 
// Group the features by the team name 
var flexible = GroupBy(
    Filter(FeatureSetByPortalItem(portal,'xxx',0,['Length', 'SURV_CREW', 'EXEMPT_COD'],false), sql),
    ['SURV_CREW'],
    [{ name: 'flexible', expression: 'Length', statistic: 'SUM' }]
);

var rigid = GroupBy(
    Filter(FeatureSetByPortalItem(portal,'xxx',1,['Length', 'SURV_CREW', 'EXEMPT_COD'],false), sql),
    ['SURV_CREW'],
    [{ name: 'rigid', expression: 'Length', statistic: 'SUM' }]
);

var concrete = GroupBy(
    Filter(FeatureSetByPortalItem(portal,'xxx',2,['Length', 'SURV_CREW', 'EXEMPT_COD'],false), sql),
    ['SURV_CREW'],
    [{ name: 'concrete', expression: 'Length', statistic: 'SUM' }]
);

var gravel = GroupBy(
    Filter(FeatureSetByPortalItem(portal,'xxx',3,['Length', 'SURV_CREW', 'EXEMPT_COD'],false), sql),
    ['SURV_CREW'],
    [{ name: 'gravel', expression: 'Length', statistic: 'SUM' }]
);

// Create empty array for features, feat object to populate array
var features = [];
var feat;

// Loop through each of the four FeatureSets and populate feature array.
for (var f in flexible) {
    feat = {
        attributes: {
            team_name: f['SURV_CREW'],
            length_by_team: f['flexible'],
        },
    };
    Push(features, feat);
}

for (var r in rigid) {
    feat = {
        attributes: {
            team_name: r['SURV_CREW'],
            length_by_team: r['rigid'],
        },
    };
    Push(features, feat);
}

for (var c in concrete) {
    feat = {
        attributes: {
            team_name: c['SURV_CREW'],
            length_by_team: c['concrete'],
        },
    };
    Push(features, feat);
}

for (var g in gravel) {
    feat = {
        attributes: {
            team_name: g['SURV_CREW'],
            length_by_team: g['gravel'],
        },
    };
    Push(features, feat);
}

var combinedDict = {
    fields: [
        { name: 'team_name', type: 'esriFieldTypeString' },
        { name: 'length_by_team', type: 'esriFieldTypeDouble' },
    ],
    geometryType: '',
    features: features,
};

// Return dictionary cast as a feature set 
return FeatureSet(Text(combinedDict));

[MCAGIS]

I have a similar issue, where I can't get the expression to work. I have followed the previous example as best as possible, but am getting an error message saying, "Test execution error: Unknown Error. Verify test data". My expression is below. Any thoughts?

// your organization's ArcGIS Online URL
var portal = Portal('https://xyzxyzxyzxyzx.com/arcgis');

// table from feature service, sub-layer id=0
var idr_maintenance = FeatureSetByPortalItem(portal, '28ab43bbb4e04a9c8d4e3860a46a3f2b', '0', ['idr_date_time', 'inspectortechnician_name'], false);

// table from feature service, sub-layer id=1
var drive_thru = FeatureSetByPortalItem(portal, '505e5af8a8cb44b0b655e965df3715fa', '1', ['_date', 'inspector_name'], false);

// table from feature service, sub-layer id=2
var unauthorized_usage = FeatureSetByPortalItem(portal, 'ab204d89388b4c58bfd76d3f34ba97a5', '2', ['_date', 'inspector_name'], false);

// table from feature service, sub-layer id=3
var master_meter = FeatureSetByPortalItem(portal, '295df1e0ad1e42189c05585ec7cf235f', '3', ['report_date', 'inspector_name'], false);

// table from feature service, sub-layer id=4
var line_replacement = FeatureSetByPortalItem(portal, '8b4f90e6178f41219d9cac88175d0544', '4', ['report_date', 'inspector_name'], false);

var features = [];
var feat;

for (var idr_maintenancefinalfeat in idr_maintenance) {
  feat = {
    attributes: {
      'Inspector_Name': idr_maintenancefinalfeat['inspectortechnician_name'],
      'Date_Reported': idr_maintenancefinalfeat['idr_date_time']
    },
  };
  Push(features, feat);
}

for (var drive_thrufinalfeat in drive_thru) {
  feat = {
    attributes: {
      'Inspector_Name': drive_thrufinalfeat['inspector_name'],
      'Date_Reported': drive_thrufinalfeat['_date']
    },
  };
  Push(features, feat);
}

for (var unauthorized_usagefinalfeat in unauthorized_usage) {
  feat = {
    attributes: {
      'Inspector_Name': unauthorized_usagefinalfeat['inspector_name'],
      'Date_Reported': unauthorized_usagefinalfeat['_date']
    },
  };
  Push(features, feat);
}

for (var master_meterfinalfeat in master_meter) {
  feat = {
    attributes: {
      'Inspector_Name': master_meterfinalfeat['inspector_name'],
      'Date_Reported': master_meterfinalfeat['report_date']
    },
  };
  Push(features, feat);
}

for (var line_replacementfinalfeat in line_replacement) {
  feat = {
    attributes: {
      'Inspector_Name': line_replacementfinalfeat['inspector_name'],
      'Date_Reported': line_replacementfinalfeat['report_date']
    },
  };
  Push(features, feat);
}

var Dict = {
    'fields': [
        {'name': 'Inspector_Name', 'type': 'esriFieldTypeString'},
	      {'name': 'Date_Reported', 'type': 'esriFieldTypeDate'},
    ],
    geometryType: '',   
    features: features
  };

// return the featureset
return FeatureSet(Text(Dict));

[KathrynWesson]

@MCAGIS, nothing stands out to me as being incorrect. Try breaking down the expression and testing the functions one at a time, if you haven't already. That might help narrow down which item is causing the error. First I'd run a test on each FeatureSetByPortalItem command, to make sure nothing screwy is going on with any of the source connections. Then I would test each "for (var x in xx_xxx)" expression block separately.