Joining two Feature Sets together and getting a count with an Arcade Data Expression

3129
2
12-07-2021 11:04 AM
Labels (2)
by Anonymous User
Not applicable
6 2 3,129

The Scenario:

This scenario uses Arcade to join two datasets with a data expression and return a count of joined features:

  1. A hosted feature service of "Zones"
  2. A hosted feature service of survey data that shares a common ID with the Zones in a 1-M relationship.

Important note: This workflow was designed for use cases with a small number of zones (<15). Growing beyond this number may result in slow loading times for your widgets.

The Datasets:

Zone Polygons

ZoneIDZone Name
0001North
0002West
0003South

Survey Data

ZoneIDSubmitter Name
0001Mark
0001Amy
0002Bob
0002Jared
0002Jhonatan

 

The Expression:

 

 

//Bring in both feature sets for the join
var zoneFS = FeatureSetByPortalItem(Portal('https://arcgis.com'),'726b8daa3a424eee94726d08487bcd86',0,['ZoneID'], true)
var surveyDataFS = FeatureSetByPortalItem(Portal('https://arcgis.com'),'544bd653c91d4920bfe184b572758af1',0,['ZoneID','SubmissionName'], false)

var joinedDict = {
  fields: [
    { name: "ZoneID", alias: "ZoneID", type: "esriFieldTypeString"},
    {name: "Registrants", type: "esriFieldTypeInteger"}
  ],
'geometryType': 'esriGeometryPolygon',
'features':[]};

var i = 0;

//for each zone in the Zone layer, count the number of records with the same ZoneID in the survey submissions
for (var t in zoneFS) {
    var tableID = t["ZoneID"]
    var tableCount = Count(Filter(surveyDataFS,"ZoneID = @tableID"))
    joinedDict.features[i] = {
        attributes: {
            ZoneID: tableID,
            Registrants: tableCount
        }
    }
i++
}

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

 

 

The Result:

ZoneIDRegistrantsFID
000120
000231
000302

 

Modified from: https://github.com/Esri/arcade-expressions/blob/master/dashboard_data/JoinLayerFieldsToTable

2 Comments