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));