Hi,
I am new to arcade scripts
I want to select unique values from two columns and sum one of them
If I go to edit Dashboard + indicator and press new data expression the following script works:
var features = FeatureSetByPortalItem( Portal('https://www.arcgis.com'), '98cca0ba2d58470b96061faa24421e66', 0, ['ID_FCC', 'RestorationArea','RestorationType'], false );
var filterFeatures = Filter(features, "RestorationType <> 'Riparian'");
var distinctFeatures = Distinct(filterFeatures,['ID_FCC','RestorationArea']);
Sum(distinctFeatures, 'RestorationArea')
If I press done the Data expressions shows: ! Unable to execute Arcade script
Is there another way to implement distinct filter to an indicator?
thank you
best regards
Adrian
Solved! Go to Solution.
As @KenBuja points out, you need to get your results into a FeatureSet. This gives you two options:
Following the linked example from Ken's post, or similar examples on GitHub, you need to create a dict object and then use that Sum(...) value to populate an attribute there.
var fs_dict = {
fields: [{name: 'Sum', 'type':'esriFieldTypeDouble'}],
geometryType: '',
features: [{attributes: {'Sum': 0}}]
}
fs_dict['features'][0]['attributes']['Sum'] = Sum(distinctFeatures, 'RestorationArea')
return FeatureSet(Text(fs_dict))
But honestly, that's way more work than it's worth. There's a simpler way.
Using the same expression you've shared in your post, simply omit the Sum and instead return the distinctFeatures FeatureSet. Then on the indicator settings, set the Value Type to Statistic, set Statistic to Sum and select the field you want to sum.
All that said, I am curious about what the Distinct function is actually doing here, since you're just returning a single sum. GroupBy can group distinct values from a field and perform a statistical calculation on any number of fields, if you were looking for the sum of the area per ID.
GroupBy(filterFeatures, 'ID_FCC', { name: 'Total_Area', expression: 'RestorationArea', statistic: 'SUM' })
A data expression expects a Featureset as a data source, so you have to do some more work when you want to return Sum. Take a look at this discussion.
As @KenBuja points out, you need to get your results into a FeatureSet. This gives you two options:
Following the linked example from Ken's post, or similar examples on GitHub, you need to create a dict object and then use that Sum(...) value to populate an attribute there.
var fs_dict = {
fields: [{name: 'Sum', 'type':'esriFieldTypeDouble'}],
geometryType: '',
features: [{attributes: {'Sum': 0}}]
}
fs_dict['features'][0]['attributes']['Sum'] = Sum(distinctFeatures, 'RestorationArea')
return FeatureSet(Text(fs_dict))
But honestly, that's way more work than it's worth. There's a simpler way.
Using the same expression you've shared in your post, simply omit the Sum and instead return the distinctFeatures FeatureSet. Then on the indicator settings, set the Value Type to Statistic, set Statistic to Sum and select the field you want to sum.
All that said, I am curious about what the Distinct function is actually doing here, since you're just returning a single sum. GroupBy can group distinct values from a field and perform a statistical calculation on any number of fields, if you were looking for the sum of the area per ID.
GroupBy(filterFeatures, 'ID_FCC', { name: 'Total_Area', expression: 'RestorationArea', statistic: 'SUM' })
Hi Josh,
Thank you for your quick response
Using Indicator's Built-In Aggregation worked for me
var features = FeatureSetByPortalItem( Portal('https://www.arcgis.com'), '98cca0ba2d58470b96061faa24421e66', 0, ['ID_FCC', 'RestorationArea','RestorationType'], false );
var filterFeatures = Filter(features, "RestorationType <> 'Riparian'");
var distinctFeatures = Distinct(filterFeatures,['ID_FCC','RestorationArea']);
return distinctFeatures
And after I use sum statistic on the field 'RestorationArea'
Group by - is not my case because I want to sum only once one ID, not to sum the area per ID
Thank you again
Adrian
Hi Josh,
I have another question regarding this topic
var features = FeatureSetByPortalItem( Portal('https://www.arcgis.com'), '98cca0ba2d58470b96061faa24421e66', 0, ['ID_FCC', 'RestorationArea','RestorationType','Project','HabitatRestored','Date'], false );
var filterFeatures = Filter(features, "RestorationType <> 'Riparian'");
var distinctFeatures = Distinct(filterFeatures,['ID_FCC','RestorationArea']);
return distinctFeatures
The above feature set return only ID_FCC and RestorationArea columns together with ROW_ID
But I want to return also the fields from FeatureSetByPortalItem
Apparently Distinct Function remove other columns
Is there a way to rejoin all the fields base on ROW_ID?
Thanks
Adrian