Create Arcade expression for ArcGIS Dashboard Pie Chart widget

[Caitlin_Todd_LCOR]

Hello, 

I'm a beginner in all things Arcade and I'm having some trouble with an Arcade expression for ArcGIS Dashboard. I have a table of names that I will be pulling into a Pie Chart widget to show all repeated names as categories, so if John Doe is repeated 5 times and Jane Doe is repeated 3 times, then John Doe gets a bigger slice of the pie chart. But the widget has a max of 300 categories and my table has over 400 records, so I need to filter the data down to only the repeated names before it tries to render in the Pie Chart. Which leads me to trying to create a data expression. 

The part I'm stuck on is figuring out what function to use to identify repeat values in a field. Online research shows quite a bit about how to find unique values, but I really need the opposite of that. 

So far I have, 

var portal = Portal('https://someportal.com/')
var tablefs = FeatureSetByPortalItem(
    portal,
    'tableportalitemid',
    1,
    ['*'],
    false
);
// Create empty dictionary of all fields you want the pie chart to reference  
var dict = { 
  fields: [ 
    { name: "wholename", type: "esriFieldTypeString" }, 
    { name: "GlobalID", type: "esriFieldTypeGlobalID" },  
  ], 
  geometryType: "", 
  features: [], 
};

I think the rest of the logic would be something like;

//Create a list of all values in tablefs wholename field

//Where wholenames are not distinct, store into an array.

//Create more logic to loop through the tablefs and if wholename value exists in array then push all matching features into dictionary 

//Finally export dictionary to featureset to reference in Pie Chart Widget.

Thanks very much for any insight and advice, 

There may be a better way..

[JohannesLindner]

You can use the GroupBy function.

// load your feature set (lines 1-8 in your code)
var dict = { 
  fields: [ 
    { name: "wholename", type: "esriFieldTypeString" }
  ], 
  geometryType: "", 
  features: [
      {"attributes": {"wholename": "John Doe"}},
      {"attributes": {"wholename": "John Doe"}},
      {"attributes": {"wholename": "John Doe"}},
      {"attributes": {"wholename": "Jane Doe"}},
      {"attributes": {"wholename": "Jane Doe"}},
      {"attributes": {"wholename": "Jane Doe"}},
      {"attributes": {"wholename": "Jane Doe"}},
      ], 
};
var fs = FeatureSet(Text(dict))

// Aggregate by wholename, use count as statistic
var aggregated_fs = GroupBy(fs, ["wholename"], {"name": "NameCount", "expression": "wholename", "statistic": "Count"})

return aggregated_fs

[Caitlin_Todd_LCOR]

Hi Johannes! 

Thank you very much for your advice and the code sample, it helped me make significant progress on my Arcade expression. I'm now at the point where features are returned, but I still need to add some more logic to it.

Is there a way to use the 'NameCount' value in an IIF statement? 

so it'd look something like

var alln = groupby(tablefs, 'wholename', {"name": "NameCount", "expression": "wholename", "statistic": "Count"});
    var dups = IIf(alln>1, alln,"")

 Many thanks! and have a great day!

Caitlin

[JohannesLindner]

GoupBy() returns a FeatureSet (the same data type as your tablefs). If I understand your code correctly, you want to filter out names that only show up once? You can use Filter() for that:

var alln = groupby(tablefs, 'wholename', {"name": "NameCount", "expression": "wholename", "statistic": "Count"});
var dups = Filter(alln, "NameCount > 1")

[Caitlin_Todd_LCOR]

Thanks! Filter did the trick there. 

That combined with the code sample you provided earlier did the trick after configuring it to loop through all the rows in the table. But when I set it with wholename as the category it showed up with equal sized pie slices per name, as seen in the screenshots below.

So I think I'll be continuing to play with the script to try to compare the GroupBy featureset with the original tablefs portal item and pull out only those names that match (and pull in their GUIDS at the same time).

Here's what the script looks like now.

var portal = Portal('https://someportal.com/')
var tablefs = FeatureSetByPortalItem(
    portal,
    'someportalid',
    1,
    ['*'],
    false
);
// Create empty dictionary of all fields you want the pie chart to reference  
var dict = { 
  fields: [ 
    { name: "wholename", type: "esriFieldTypeString" },
    { name: "GlobalID", type: "esriFieldTypeGlobalID" },  
  ], 
  geometryType: "", 
  features: [], 
};
for (var f in tablefs) {
    var id = (['GlobalID'])
    var n = (['wholename'])
    var alln = groupby(tablefs, 'wholename', {name: "NameCount", expression: "wholename", statistic: "Count"});
    var dups = Filter(alln, "NameCount>1")
}
return dups

Pie Chart with NameCount as a category

 Pie Chart showing counts of repeated names

Pie Chart with wholename as a category

I think looking for matches between Groupby function and the tablefs portal item may be outside the scope of this question? 

Thanks again for all your help, 

Caitlin

[JohannesLindner · Accepted solution]

So, this is a case of me not knowing how Dashboard pie charts work... I thought you would supply a numeric and a label column, in which case the GroupBy would have sufficed. But you only supply one column and ArcGIS groups those values on its own.

Reading your question again, you correctly outlined the general process, although you can do it without using an extra dictionary:

// load your feature set
var dict = { 
  fields: [ 
    { name: "wholename", type: "esriFieldTypeString" }
  ], 
  geometryType: "", 
  features: [
      {"attributes": {"wholename": "abc"}},
      {"attributes": {"wholename": "abc"}},
      {"attributes": {"wholename": "def"}}, // singular name
      {"attributes": {"wholename": "ghi"}},
      {"attributes": {"wholename": "ghi"}},
      {"attributes": {"wholename": "ghi"}},
      ], 
};
var tablefs = FeatureSet(Text(dict))

// group by name, only keep multiple occurences
var grouped_fs = GroupBy(tablefs, "wholename", {"name": "NameCount", "expression": "wholename", "statistic": "Count"})
var duplicate_fs = Filter(grouped_fs, "NameCount > 1")

// construct an SQL filter statement with which to filter the original data
// wholename IN ('Name1', 'Name2', 'Name3')
var filter_names = []
for(var f in duplicate_fs) {
    Push(filter_names, f.wholename)
}
var filter_statement = `wholename IN ('${Concatenate(filter_names, "', '")}')`

// filter the original feature set with this statement and return the result
// this contains all the original rows with duplicate names
return Filter(tablefs, filter_statement)

[Caitlin_Todd_LCOR]

Success! Thank you so much!! the screenshot below is the breakdown of wholename and filtered to where count is greater than 1 (also grouped all counts of 2 into one category; the big gray slice).

Being able to retrieve the entire tablefs schema allows for a bonus functionality too. By setting layer actions between widgets off of guid fields I can replicate the functionality of a 1:M relationship class 😄 For example, select one or more pie slices to auto filter a corresponding list widget. Allow user to select multiple list items and have that filter the map widget. 

I'm attaching the final, working code for others to reference. 

//ID portal and portal item to reference in expression
var portal = Portal('https://someportal.com/')
var tablefs = FeatureSetByPortalItem(
    portal,
    'someportalid',
    1,
    ['*'],
    false
);
//go through every row in table, groupby name and isolate where count of name is greater than 1
for (var f in tablefs) {
    var gid = (['GlobalID'])
    var n = (['wholename'])
    var alln = groupby(tablefs, 'wholename', {name: "NameCount", expression: "wholename", statistic: "Count"});
    var dups = Filter(alln, "NameCount>1")
//match names to only return names that are duplicated    
var filterednames = []
    for(var f in dups){
    Push(filterednames, f.wholename)
    }
}
//filter statement used to push out that match those that were duplicated
var filter_statement = `wholename IN ('${Concatenate(filterednames, "', '")}')`
//return all tablefs rows where wholename matches the names identified as duplicate
return Filter(tablefs, filter_statement)

Final pie chart showing the top repeated names with different sized pie slices