Select to view content in your preferred language

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

5237
3
Jump to solution
02-28-2023 01:26 PM
C_McNamara
Occasional Contributor

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:

Prelim Advanced Filter.PNG

Siteplan - Advanced Filter.PNG

 

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

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Esteemed Contributor

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: []
}
- Josh Carlson
Kendall County GIS

View solution in original post

3 Replies
jcarlson
MVP Esteemed Contributor

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: []
}
- Josh Carlson
Kendall County GIS
C_McNamara
Occasional Contributor

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

Enjoy the rest of your week!

Best,

Colin

0 Kudos
OussamaMerhi
Occasional Contributor

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

0 Kudos