Arcade Data Expression - Filtering Two Layers Before Merging them into a Single FeatureSet

C_McNamara

Hello!

I am trying to filter two separate datasets before combining them into a single FeatureSet to be used in a dashboard but I am cannot seem to figure out how to filter these items correctly. I am able to filter these items in Insights and Pro but I don't seem to get the syntax right in arcade.

Below are two images from Insights with the correct Advanced Filters:

 

 

Any idea how to filter my datasets according to this query before I merge them together? Below is my current arcade script for combining these datasets.

var PrelimPlans = FeatureSetByPortalItem(Portal('https://www.arcgis.com/'),'2a7c6c6f86b14253b1ecf079e3f4e6dd',1,['*'],false);
var SitePlan = FeatureSetByPortalItem(Portal('https://www.arcgis.com/'),'33c2a66277c34153907ba5d14484a24b',3,['*'],false);

var combinedDict = { 
  'fields': [ 
    {name: "APNO", type:"esriFieldTypeString" },
    {name: "PROJNAME", type: "esriFieldTypeString" },
    {name: "LOC", type: "esriFieldTypeString" },
    {name: "CBPTEAM", type: "esriFieldTypeString" },
    {name: "PBDATE", type: "esriFieldTypeDate" },
    {name: "PENDING", type: "esriFieldTypeString" },
    {name: "DECISIONTYPE", type: "esriFieldTypeString" },
    {name: "STANDALONE", type: "esriFieldTypeString" },
    {name: "APPR_DU_TOT", type: "esriFieldTypeDouble" },
    {name: "APPR_DU_SF", type: "esriFieldTypeDouble" }, 
    {name: "APPR_DU_MF", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_TOT", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_OFF", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_RET", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_IND", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_OTH", type: "esriFieldTypeDouble" },

  ], 
  geometryType: "", 
  features: [], 
};

var i = 0; 
// Loop through each FeatureSet and store its attributes 
for (var t in PrelimPlans) { 
  combinedDict.features[i++] = { 
    attributes: {
      APNO: t["APNO"],
      PROJNAME: t["PROJNAME"],
      CBPTEAM: t["CBPTEAM"],
      PBDATE: Number(t["PBDATE"]),
      PENDING: t["PENDING"],
      DECISIONTYPE: t["DECISIONTYPE"],
      APPR_DU_TOT: t["APPR_DU_TOT"],
      APPR_DU_SF: t["APPR_DU_SF"], 
      APPR_DU_MF: t["APPR_DU_MF"],
      APPR_SQFT_TOT: t["APPR_SQFT_TOT"],
      APPR_SQFT_OFF: t["APPR_SQFT_OFF"],
      APPR_SQFT_RET: t["APPR_SQFT_RET"],
      APPR_SQFT_IND: t["APPR_SQFT_IND"],
      APPR_SQFT_OTH: t["APPR_SQFT_OTH"],
    }, 
  }; 
} 

for (var m in SitePlan) { 
  combinedDict.features[i++] = { 
    attributes: { 
      APNO: m["APNO"],
      PROJNAME: m["PROJNAME"],
      CBPTEAM: m["CBPTEAM"],
      PBDATE: Number(m["PBDATE"]),
      PENDING: m["PENDING"],
      DECISIONTYPE: m["DECISIONTYPE"],
      STANDALONE: m["STANDALONE"],
      APPR_DU_TOT: m["APPR_DU_TOT"],
      APPR_DU_SF: m["APPR_DU_SF"], 
      APPR_DU_MF: m["APPR_DU_MF"],
      APPR_SQFT_TOT: m["APPR_SQFT_TOT"],
      APPR_SQFT_OFF: m["APPR_SQFT_OFF"],
      APPR_SQFT_RET: m["APPR_SQFT_RET"],
      APPR_SQFT_IND: m["APPR_SQFT_IND"],
      APPR_SQFT_OTH: m["APPR_SQFT_OTH"],
    }, 
  }; 
}
return FeatureSet(Text(combinedDict))

 

Any help would be greatly appreciated!

All the best,

Colin

jcarlson

You can apply the Filter function to each. The second parameter in a Filter is a SQL expression.

var PrelimPlans = Filter(
    FeatureSetByPortalItem(Portal('https://www.arcgis.com/'),'2a7c6c6f86b14253b1ecf079e3f4e6dd',1,['*'],false),
    "some_field = 'a value' OR another_field < 12"
)

 And just a couple notes on the rest of the expression. If the appended features have the same fields for both input layers, you could do it all with a nested loop and keep your code shorter. And if you're on AGOL or a more recent version of Portal, there's no need for setting an index variable and incrementing, that's an outdated method. Push can take care of it for you.

var sets = [
    Filter(FeatureSetByPortalItem(Portal('https://www.arcgis.com/'),'2a7c6c6f86b14253b1ecf079e3f4e6dd',1,['*'],false), 'some SQL expression'),
    Filter(FeatureSetByPortalItem(Portal('https://www.arcgis.com/'),'33c2a66277c34153907ba5d14484a24b',3,['*'],false), 'some SQL expression')
]

var combinedDict = { 
  'fields': [ 
    {name: "APNO", type:"esriFieldTypeString" },
    {name: "PROJNAME", type: "esriFieldTypeString" },
    {name: "LOC", type: "esriFieldTypeString" },
    {name: "CBPTEAM", type: "esriFieldTypeString" },
    {name: "PBDATE", type: "esriFieldTypeDate" },
    {name: "PENDING", type: "esriFieldTypeString" },
    {name: "DECISIONTYPE", type: "esriFieldTypeString" },
    {name: "STANDALONE", type: "esriFieldTypeString" },
    {name: "APPR_DU_TOT", type: "esriFieldTypeDouble" },
    {name: "APPR_DU_SF", type: "esriFieldTypeDouble" }, 
    {name: "APPR_DU_MF", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_TOT", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_OFF", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_RET", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_IND", type: "esriFieldTypeDouble" },
    {name: "APPR_SQFT_OTH", type: "esriFieldTypeDouble" },

  ], 
  geometryType: "", 
  features: [], 
};

for (var set in sets) {
    for (var feat in sets[set]) {
        Push(
            combinedDict['features'],
            {attributes: {
                APNO: feat["APNO"],
                PROJNAME: feat["PROJNAME"],
                CBPTEAM: feat["CBPTEAM"],
                PBDATE: Number(feat["PBDATE"]),
                PENDING: feat["PENDING"],
                DECISIONTYPE: feat["DECISIONTYPE"],
                APPR_DU_TOT: feat["APPR_DU_TOT"],
                APPR_DU_SF: feat["APPR_DU_SF"], 
                APPR_DU_MF: feat["APPR_DU_MF"],
                APPR_SQFT_TOT: feat["APPR_SQFT_TOT"],
                APPR_SQFT_OFF: feat["APPR_SQFT_OFF"],
                APPR_SQFT_RET: feat["APPR_SQFT_RET"],
                APPR_SQFT_IND: feat["APPR_SQFT_IND"],
                APPR_SQFT_OTH: feat["APPR_SQFT_OTH"],
            }}
        )
    }
}

return FeatureSet(Text(combinedDict))

Also, if the output fields are coming wholesale from the input FeatureSets, you can use the Schema function to help with that as well.

var combinedDict = {
    fields: Schema(some_FeatureSet)['fields'],
    geometryType: '',
    features: []
}