Data Expression: Combined Category Count for Multiple Fields with Same Domain

SamSlater

I have points that are given a primary category field and secondary category field of the same domain. I am attempting to use a pie chart to display the total count of points per category from both fields. I am trying to create a data expression to sum the count of categories from both fields:

'Comment_Type'

'Secondary_Type'

 

 

 

 

 

 

 

 

 

 

var p = 'https://xyzxyzxy.maps.arcgis.com'

var itemId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

var fs = FeatureSetByPortalItem(Portal(p), itemId, 0, ['*'], false)

var comment_stats = GroupBy(fs, ['Comment_Type'],

                [ { name: 'Comment_Type',

                    expression: 'Comment_Type',

                  statistic: 'COUNT' },

                    { name: 'Secondary_Type',

                   expression: 'Secondary_Type',

                    statistic: 'COUNT' },

                 ])

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How do I get the table to use the domain label for the first column?

Why does the Secondary_Type column not match its actual values?

Is there a more efficient way of doing this?

var comment_totals = GroupBy(comment_stats, ['Comment_Type'],

                   [ { name: 'Totals',

                   expression: 'Comment_Type + Secondary_Type',

                   statistic: 'SUM' },

                    ])

return comment_totals

 

 

kenbuja

I don't think that's possible with single GroupBy, but I'd love to be proved wrong.

You'll have to do two GroupBys, one for each field. That will give you the count for each of the fields. Then you can combine them into a single table with the counts of both and their sums

Unfortunately, the GroupBy does strip the domain information from your result. But you can add that back in when combining the tables by looping through all of the domain codes. This code loops through all the domain codes (and this assumes you're using the same domain for both fields) and filters the grouped tables to get their counts.

var fs = FeatureSetByPortalItem(Portal("your portal"), itemID, 0, ["*"], false);
var primaryField = "Comment_Type";
var primary = GroupBy(
  fs,
  [primaryField],
  [{ name: "Primary Count", expression: primaryField, statistic: "Count" }]
);
var secondaryField = "Secondary_Type";
var secondary = GroupBy(
  fs,
  [secondaryField],
  [{ name: "Secondary Count", expression: secondaryField, statistic: "Count" }]
);

var theDomain = Domain(fs, primaryField).codedValues;
//return theDomain;

var features = [];

for (var dom of theDomain) {
  var code = dom.code;

  var filterPrime = Filter(primary, "Comment_Type = @code");
  var filterSecondary = Filter(secondary, "Secondary_Type = @code");
  var primaryCount = iif(
    Count(filterPrime) > 0,
    First(filterPrime)["Primary Count"],
    0
  );
  var secondaryCount = iif(
    Count(filterSecondary) > 0,
    First(filterSecondary)["Secondary Count"],
    0
  );
  Push(
    features,
    {
      attributes:
        {
          Comment: dom.name,
          Primary: primaryCount,
          Secondary: secondaryCount,
          Total: primaryCount + secondaryCount
        }
    }
  );
}

FeatureSet(
  {
    fields: [
      { name: "Comment", type: "esriFieldTypeString" },
      { name: "Primary", type: "esriFieldTypeInteger" },
      { name: "Secondary", type: "esriFieldTypeInteger" },
      { name: "Total", type: "esriFieldTypeInteger" }
    ],
    features: features
  }
);

 This code worked properly for my test data and I hopefully didn't get anything wrong when substituting in your fields.