topic Re: How to Use Arcade GroupBy with Top and OrderBy Expressions in Developers Questions

How to Use Arcade GroupBy with Top and OrderBy Expressions

izk_1989 — Fri, 21 May 2021 00:57:37 GMT

Hello,

I am struggling with using the Arcade's GroupBy with the Top and OrderBy Functions.

Basically I have a polygon layer which references and counts the number of features from another point layer, and I want to display in the popup the top 3 attributes based on counts within each polygon.

I have attached a file the arcade expression code I have so far for the polygon layer, however it is not working at moment (nothing being written in the popup). I am trying to get it to count the total number of point features within any given polygon, group by the attribute value of the facility_name field and find the top 3 facilities that have the highest point count

I am looking for the popup to show something like the following when a polygon is selected:

The Top 3 Facilities in this area are:

1. Facility A: 400 Patients

2. Facility B: 200 Patients

3. Facility C: 100 Patients

Greatly appreciate any help or advice with the code.

Regards,

Isaac

Re: How to Use Arcade GroupBy with Top and OrderBy Expressions

KenBuja — Fri, 21 May 2021 12:28:38 GMT

One thing I noticed is you used the line

var topFacilities = Top(OrderBy(Filter(stats, "facility_name <> ''"),"ClinicCount desc"), 3)

but later loop through a variable you haven't declared

for(var item in topClinics){

Re: How to Use Arcade GroupBy with Top and OrderBy Expressions

XanderBakker — Fri, 21 May 2021 13:51:05 GMT

Hi @izk_1989 ,

In addition to what Ken correctly pointed out, there is another error: you don't have a total field, you have ClinicCount. I also formatted the result a bit differently.

// Reference the points feature layer var patientsDA = FeatureSetByName($map,"PointsLayer") // Count the number of patients within each polygon var cntPatients = Intersects(patientsDA, $feature) // Group patients by their facility and count var stats = GroupBy(cntPatients, "facility_name", [ {name: "ClinicCount", expression: "facility_name", statistic: "COUNT"} ]) // Order the results in descending order by the total counts var topFacilities = Top(OrderBy(Filter(stats, "facility_name <> ''"),"ClinicCount desc"), 3) // Popup placeholder var result = 'The Top Facilities in this area are:' // Write the results to the popup if(Count(topFacilities) == 0){ return "No Patients in this area" } var num = 0 // Format the results for display in popup for(var item in topFacilities){ num++ var num_clinics = item["ClinicCount"] var clinic_name = item["facility_name"] result += TextFormatting.NewLine + num + ". " + clinic_name + ": " + num_clinics + " Patients" } return result

Re: How to Use Arcade GroupBy with Top and OrderBy Expressions

izk_1989 — Fri, 21 May 2021 18:45:19 GMT

Thank you Ken, yes that is an error the topClinics should be using the topFacilities field

Re: How to Use Arcade GroupBy with Top and OrderBy Expressions

izk_1989 — Fri, 21 May 2021 19:06:31 GMT

Thanks Xander, this solution works!

Re: How to Use Arcade GroupBy with Top and OrderBy Expressions

MGT8C2U — Fri, 15 Jul 2022 14:05:46 GMT

Is there a way to format the result variable text to show "The Top Facilites in this area are:" with an underline? Or can you format the entire result with HTML somehow?

Re: How to Use Arcade GroupBy with Top and OrderBy Expressions

Anonymous User — Wed, 19 Oct 2022 20:54:01 GMT

Hello,

I found this thread while searching for help with using Orderby, however, I'm very new to Arcade and I am not sure where to insert the function. Here is my code that I created already.

var intersectLayer =Intersects(FeatureSetByName($map, "CAMS Address Points"), $feature) var feature = FeatureSetByName($map, "CAMS Address Points") var results = "" for (var f in intersectLayer){ if (results ==""){ results = f.Number; } else{ results += ", " + f.Number + iif(f.NumSuffix == '<Null>', ' ', f.NumSuffix); } } return results

Basically, I have a parcel and I am trying to list the address points within that parcel.

Thank you in advance.

Re: How to Use Arcade GroupBy with Top and OrderBy Expressions

VeronicaS — Mon, 20 Mar 2023 20:18:21 GMT

Hi Xander.

Is there any way to do this same type of thing with line features? I have a feature layer that is "Paint Lines 2017". This layer contains 2 fields "PAINT_WID' and 'PAINT_COL'. I have been trying to modify your code above to fit a solution for what I need. I need to know the quantity of paint lines inside of a polygon based on their size ('PAINT_WID') and their color ('PAINT_COL') and I am trying to return the quantities of each type in a popup in Arcgis Online. I attached a snip of what I am trying to achieve. I appreciate your help!