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 · Accepted solution]

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: []
}

[C_McNamara]

Thank you, Josh @jcarlson . I really appreciate your help! 

Enjoy the rest of your week!

Best,

Colin

[OussamaMerhi]

Hi,

Thank you for this code. I have used the same feature ID for the two features and that leads to have double the number of records which doesn't happen. It gives me only the number of records of one feature only.

Hope to hear from you soon.

Best,

Oussama