topic Append tables in data expression in ArcGIS Dashboards Questions

Append tables in data expression

RohitJaikumar — Fri, 04 Nov 2022 17:21:49 GMT

I am new to using Arcade expressions. I want a dashboard to aggregate data from 4 different feature layers into a table or a chart. The data has four maps with polyline feature layer representing each year 2023, 2026, 2036 and 2045. I was able to create aggregated data from individual layers by repeating the following code for each layer's data source.

var fs = FeatureSetByPortalItem(Portal('https://tamu.maps.arcgis.com'), '302880accba645198a5174df9f51b300', 3,['STREET','FAC_TYPE','NUM_LANES','MTP_ID'])
var agg = GroupBy(fs,['MTP_ID','FAC_TYPE','STREET'],[{name:'Lanes',expression:'NUM_LANES',statistic: 'MAX'}])
var agsum = GroupBy(agg,['MTP_ID','FAC_TYPE'],[{name:'Lanes',expression:'Lanes',statistic:'SUM'}])
return agsum

Ideally I want them in a single table which shows the number of lanes in each year for each MTP ID. I am not sure which function to use to append tables with arcade expressions. Any help would be appreciated.

Re: Append tables in data expression

jcarlson — Fri, 04 Nov 2022 17:57:12 GMT

If all you need is to merge your tables together, you'll need to use loops and push the features into a single array. There's currently no easier way to get the features from one set out into an array, unfortunately.

var merge_arr = [] // repeat this loop for each of your grouped featuresets for (var f in fs){ Push(merge_arr, f) } // this assumes your schemas are all the same between layers var merge_dict = { fields: Schema(fs)['fields'], geometryType: '', features: merge_arr } var merge_fs = FeatureSet(Text(merge_dict)) return merge_fs

There are probably some adjustments you'll need to make to this for your particular data, but this should give you the general idea.

Re: Append tables in data expression

RohitJaikumar — Fri, 04 Nov 2022 20:13:43 GMT

Thanks for the reply. I think the solution almost works. I think I have no idea how to repeat the for loop to add rows to the merge_arr. It only returns values for 2045 as that's the last loop. Here's what I have

var fs = FeatureSetByPortalItem(Portal('https://tamu.maps.arcgis.com'), '302880accba645198a5174df9f51b300', 3,['FY','STREET','FAC_TYPE','NUM_LANES','MTP_ID'])
var fs26 = FeatureSetByPortalItem(Portal('https://tamu.maps.arcgis.com'), '302880accba645198a5174df9f51b300', 2,['FY','STREET','FAC_TYPE','NUM_LANES','MTP_ID'])
var fs36 = FeatureSetByPortalItem(Portal('https://tamu.maps.arcgis.com'), '302880accba645198a5174df9f51b300', 1,['FY','STREET','FAC_TYPE','NUM_LANES','MTP_ID'])
var fs45 = FeatureSetByPortalItem(Portal('https://tamu.maps.arcgis.com'), '302880accba645198a5174df9f51b300', 0,['FY','STREET','FAC_TYPE','NUM_LANES','MTP_ID'])
var merge_arr = []

// repeat this loop for each of your grouped featuresets
for (var f in fs){
Push(merge_arr, f)
}
for (var f26 in fs26){
Push(merge_arr, f26)
}
for (var f36 in fs36){
Push(merge_arr, f36)
}
for (var f45 in fs45){
Push(merge_arr, f45)
}
// this assumes your schemas are all the same between layers
var merge_dict = {
fields: Schema(fs)['fields'],
geometryType: '',
features: merge_arr
}

var merge_fs = FeatureSet(Text(merge_dict))

var agg = GroupBy(merge_fs,['FY','MTP_ID','FAC_TYPE','STREET'],[{name:'Lanes',expression:'NUM_LANES',statistic: 'MAX'}])
var agsum = GroupBy(agg,['FY','MTP_ID','FAC_TYPE'],[{name:'Lanes',expression:'Lanes',statistic:'SUM'}])
return agsum