topic Re: Summarize table in arcade for Popup in ArcGIS Online Questions

Summarize table in arcade for Popup

aroininen — Fri, 05 Mar 2021 15:39:40 GMT

Hello Again folks,

I feel I may be exhausting my question limit - much appreciation to the community - I am looking into a course.

I'm trying to summarize a field in arcade and get statistics from the other fields for a result similar to this:

Is this possible in Arcade?

I have looked a Paul Bakers post and the Spanish one and many others but I just can't seem to make anything work

I think Groupby is the option but I just don't know get the sums from the other fields

also do I need to use intersect as I want to get results from the whole dataset if so I can intersect with the municipal border as all developments are within

I got this far with this expression (excuse the radon variables!):

var intersectArea = Intersects(FeatureSetByName($map,"Planning_Layers - Development Applications"), $feature)
var treeList = groupBy(intersectArea, "PRStatus",
{name:"count", expression:"PRStatus" , statistic:"COUNT"})

var topFeatures = Top(OrderBy(treeList, "count desc"),10)

var treeList = ''
for (var topFeature in topFeatures) {

treeList += topFeature.PRStatus + " (" + topFeature.count + ")" + TextFormatting.NewLine

}
return treeList

Thanks again

Aaron

Re: Summarize table in arcade for Popup

jcarlson — Fri, 05 Mar 2021 16:38:21 GMT

You're right, GroupBy is what you want. Since you're just grouping by a single field, and performing basic sums, it's not too complex.

Personally, I find it's easier to create the statistics parameter separately, for the sake of keeping everything straight.

var stats_list = [ { name: 'Count_PRStatus', expression: 1, statistic: 'COUNT' }, { name: 'Sum_Units', expression: 'Units', statistic: 'SUM' }, { name: 'Sum_SingleD', expression: 'SingleD', statistic: 'SUM' }, ... ] GroupBy(intersectArea, "PRStatus", stats_list)

If you're looking to just return statistics from the entire dataset, there's not really a need to intersect. That being the case, you might adjust your FeatureSetByName function as follows:

FeatureSetByName($map,"Planning_Layers - Development Applications", ['field1', 'field2', etc], false)

Including a field list tells the script to grab only the fields you need, and setting the includeGeometry parameter to false will improve your script performance a lot by not loading the feature geometry and just working with the attributes separately.

Then just use GroupBy with the featureset directly.

Re: Summarize table in arcade for Popup

aroininen — Fri, 05 Mar 2021 16:26:45 GMT

WOW thanks!!!!

close!!

(FeatureSetByName($map,"Planning_Layers - Development Applications", ['PRStatus', 'Units', 'SingleD'], false)

var stats_list = [
{ name: 'Count_PRStatus', expression: 1, statistic: 'COUNT' },
{ name: 'Sum_Units', expression: 'Units', statistic: 'SUM' },

{ name: 'Sum_SingleD', expression: 'SingleD', statistic: 'SUM' },
]

GroupBy("PRStatus", stats_list)

Return Stats_list

what did I miss?

Re: Summarize table in arcade for Popup

jcarlson — Fri, 05 Mar 2021 16:39:55 GMT

It should be FeatureSetByName(...), so try taking the parenthesis off of the front. That was a typo in my earlier response.

Also, you'll still need to bring the FeatureSet into the GroupBy function, so assign the featureset to its own variable, then give that variable as the first parameter of GroupBy.

Re: Summarize table in arcade for Popup

aroininen — Fri, 05 Mar 2021 17:00:17 GMT

my apologies

what am I not doing...?

Thanks

var planStat = (FeatureSetByName)($map,"Planning_Layers - Development Applications", ['PRStatus', 'Units', 'SingleD'], false)

var stats_list = [
{ name: 'Count_PRStatus', expression: 1, statistic: 'COUNT' },
{ name: 'Sum_Units', expression: 'Units', statistic: 'SUM' },

{ name: 'Sum_SingleD', expression: 'SingleD', statistic: 'SUM' },
]

GroupBy(planStat,"PRStatus", stats_list)

I get this

Execution Error:h.charAt is not a function

Re: Summarize table in arcade for Popup

jcarlson — Fri, 05 Mar 2021 17:01:42 GMT

var planStat = FeatureSetByName($map,"Planning_Layers - Development Applications", ['PRStatus', 'Units', 'SingleD'], false)

Re: Summarize table in arcade for Popup

aroininen — Fri, 05 Mar 2021 17:59:17 GMT

keep getting error

Execution Error:Illegal Value: GroupBy

This is the layer in the map that I am using the expression on: Planning_Layers - Development Applications - Master

should this be something different like the map name...?

var planStat = FeatureSetByName($map,"Planning_Layers - Development Applications - Master", ['PRStatus', 'Units', 'SingleD'], false)

var stats_list = [
{ name: 'Count_PRStatus', expression: 1, statistic: 'COUNT' },
{ name: 'Sum_Units', expression: 'Units', statistic: 'SUM' },

{ name: 'Sum_SingleD', expression: 'SingleD', statistic: 'SUM' },
]

GroupBy(planStat,"PRStatus", stats_list)
Return stats_list

thanks!!

Re: Summarize table in arcade for Popup

aroininen — Fri, 05 Mar 2021 20:05:15 GMT

I was able to adjust my original expression to no use intersect though!

the one that just returns PLSatus Counts

Thanks!

Re: Summarize table in arcade for Popup

jcarlson — Fri, 05 Mar 2021 21:55:50 GMT

Make sure that your return is giving you the GroupBy, not the stats_list.

return GroupBy(...)

Re: Summarize table in arcade for Popup

aroininen — Sat, 06 Mar 2021 16:07:46 GMT

Thanks for all your help Josh but I cannot work out this syntax for the return... I will keep trying..

var planStat = FeatureSetByName($map,"Planning_Layers - Development Applications - Master", ['PRStatus', 'Units', 'SingleD'], false)

var stats_list = [
{ name: 'Count_PRStatus', expression: 1, statistic: 'COUNT' },
{ name: 'Sum_Units', expression: 'Units', statistic: 'SUM' },

{ name: 'Sum_SingleD', expression: 'SingleD', statistic: 'SUM' },
]

Return GroupBy(planStat,"PRStatus", stats_list);

error Execution Error:Illegal Value: GroupBy

or this way - Return; GroupBy(planStat,"PRStatus", stats_list);

gives me this:

thanks again Aaron

Re: Summarize table in arcade for Popup

aroininen — Tue, 09 Mar 2021 15:28:59 GMT

Hello Again,

I had a bit of a break though I am able to display total units by Status with this expression!:

Problem is if I keep adding more fields for statistics (i.e Semi, Row) the result is always the last Groupby

I tried editing the last statement to add more stats to result but I get nothing

here is the expression I am using:

I don't need to order this but if I remove it doesn't work that why I just use top 10

var planstat = FeatureSetByName($map,"Planning_Layers - Development Applications - master", ['PRStatus', 'Units', 'SingleD'], false)
var treeList = groupBy(planstat, "PRStatus",
{name:"count", expression:"PRStatus" , statistic:"COUNT"})
var treeList = groupBy(planstat, "PRStatus",
{name:"count", expression:"Units" , statistic:"SUM"})

var topFeatures = Top(OrderBy(treeList, "count desc"),10)

var treeList = ''
for (var topFeature in topFeatures) {

treeList += topFeature.PRStatus +" (" + topFeature.COUNT + ")"+ TextFormatting.NewLine

}
return treeList

If I get this last bit figured out that would be great but I can live with what I have too

Thanks again community!

Re: Summarize table in arcade for Popup

DougBrowning — Wed, 19 Oct 2022 21:05:05 GMT

@jcarlson I have a similar question on OrderBy. It is showing the total as the first element but with no label. Then it also does null with no label. Would be nice if it said Null of course. But what i want is to not show the total. The help says nothing about a total. So I figure pop the first element but there is no pop function and Erase will only take array not FeatureSet. Also Array cannot convert a FeatureSet.

I just want to remove the first one without having to check manually. I see Top in there but that wont work.

var plots = Intersects($feature,FeatureSetByName($map,"TerrestrialIndicators", ["RH_BioticIntegrity"], false)); var groupOut = OrderBy(GroupBy(plots, ['RH_BioticIntegrity'], [ { name: 'outputStatName', expression: 'RH_BioticIntegrity', statistic: 'COUNT' } ]),'RH_BioticIntegrity') var display = '' for (var grp in groupOut) { display = display + grp.RH_BioticIntegrity + " - " + grp.outputStatName + TextFormatting.NewLine } return display

thanks

Re: Summarize table in arcade for Popup

jcarlson — Thu, 20 Oct 2022 14:03:57 GMT

Odd. It shouldn't be including a total if you haven't somehow asked for one...

I'd recommend using your FeatureSet to create an Array, then subset the array to remove the first item and use Concatenate to build a newline-separated string. I wish there were an easy way to get an Array from a FeatureSet.

var out_arr = [] for (var grp in groupOut){ Push( out_arr, `${grp.RH_BioticIntegrity} - ${grp.outputStatName}` ) } return Concatenate( Slice(out_arr, 1), '\n' )

Re: Summarize table in arcade for Popup

DougBrowning — Thu, 20 Oct 2022 15:01:04 GMT

Funny I just posted that "I wish there were an easy way to get an Array from a FeatureSet." Did you see that one?

But yea I see nothing in the doc that says Order by returns a Total but there it is.

Thanks I will look at it. For now I just left it in. Wait just noted that must not be the total. I dont know what it is number of records?? These are all 16.

What the heck is that first number with no label?

thanks

Re: Summarize table in arcade for Popup

jcarlson — Thu, 20 Oct 2022 15:10:18 GMT

I missed that! Y'know, I thought I had a good handle on Arcade before all this... But then, I couldn't explain anything about how cars work either, even though I know how to drive one.

I'd check the tables of the input layers to make sure you don't have any null records hiding out. Or try throwing some Console functions in to check intermediate outputs.

Re: Summarize table in arcade for Popup

DougBrowning — Thu, 20 Oct 2022 15:18:15 GMT

If you look close the second blank number is the Nulls. I have no idea what the 16 is now.

Re: Summarize table in arcade for Popup

DougBrowning — Thu, 20 Oct 2022 16:54:37 GMT

I got this working now and added code for Nulls thanks. Still no idea what the heck the first number is. Not in the help that I see. Weird 16 makes no sense.

var plots = Intersects($feature,FeatureSetByName($map,"TerrestrialIndicators", ["RH_HydrologicFunction"], false)); var groupOut = OrderBy(GroupBy(plots, ['RH_HydrologicFunction'], [ { name: 'outputStatName', expression: 'RH_HydrologicFunction', statistic: 'COUNT' } ]),'RH_HydrologicFunction') var out_arr = [] for (var grp in groupOut){ if (IsEmpty(grp.RH_HydrologicFunction)) { Push(out_arr," Nulls-" + grp.outputStatName) } else{ Push(out_arr,grp.RH_HydrologicFunction + "-" + grp.outputStatName) } } return Concatenate( Slice(out_arr, 1), ', ' )