Create Nested Group By Arcade in a List

[LindseyStone]

I'm trying to create a special arcade function in a Dashboard List (Enterprise 10.9.1) that finds the unique between two fields and then counts the number of one field.  The below screenshot is a example of data.  I have entries with multiple subnetworks.  Within the subnetworks there is accounts, the accounts can be duplicated across the same subnetwork and across other subnetworks.  I want to find only where the subnetwork + account are unique (this way the accounts that run across multiple subnetworks can be counted in each).  Then once I find the unique I want a count of each account  within each Subnetwork.  Below is utilizing the GroupBy function that I get the first GroupBy to work, but the second GroupBy returns a blank table.  I also tried the Distinct function and I get similar results and the Distinct tends to crash the entire Dashboard.

var portal = Portal(portal url);
var secondary = FeatureSetByPortalItem(portal,'1bb7df369ca44c8880d65011fa3d69e6',40,['*'],false)
var service = Filter(secondary, 'lifecyclestatus = 8');
var sub = GroupBy(service,
   [
       {name: 'Subnet', expression: 'SUBNETWORKNAME'},
       {name: 'acct', expression: 'account'}
    ],
    [
      { name: 'SubAccountCount', expression: '1', statistic: 'COUNT' },
    ]
);
var gsub = GroupBy(sub,
    ['Subnet'],
    [
      { name: 'Subnetwork Count', expression: '1', statistic: 'COUNT' },
    ]
);

return gsub

[jcarlson]

It seems like it ought to work, based on how you've written it. Try opening your browser's Developer Tools and watch the network tab when you run the expression. What request is it sending to your server? Is it truly returning an empty set, or is it just a long-running expression? I've written some really awful Arcade in the past that took over 5 minutes to actually return something.

[KenBuja]

Yes, if I run your logic on my dataset, it does return the expected nested group

This is the result of the first GroupBy

and this is the result of the second GroupBy

[LindseyStone]

@KenBuja 

Can you screenshot what you have under line 46.  The second GroupBy does not declare that a variable so what are returning?  Also, the first group by (GB) from your initial feature service and not the filtered one.

Also,  This is what I get when I test mine.

[KenBuja]

It's an implicit return, so line 46 was the last line of code

[LindseyStone]

@jcarlson when watching the network log of the browser tools when I run the first group by, I get the results in a matter of a second and the log shows

But when I run the it with the second Group By the blank result window pops up immediately as I have screenshot above, but the log just repeats pulls the same query over and over and over again.  I let it sit for 5 minutes and it just continues.

[LindseyStone · Accepted solution]

I think I found a possible explanation of my issue.  The results in my first group by should return 13,000 results but it is only returning 2,000.  So when I apply the second group by, I think it puts it in a never ending looping because it can't pull the data correctly.  I found the below thread that describes a similar experience.  Upon testing, if I don't apply a group by function to my data I get all records OR if apply a group by function to my data that results in less than 2000 records than it correctly works.

https://community.esri.com/t5/arcgis-dashboards-questions/arcgis-dashboard-arcade-featureset-express...

If I change the Maximum Number of Records Returned by Server on the service in Server manager to higher than my results (14,000) then my coding works.  However, I know increasing that number that high is going to dramatically impact my performance of the service so I will not be going that route.