topic Re: GroupBy() Function Trouble in ArcGIS Online Developers Questions

GroupBy() Function Trouble

amrodri01 — Wed, 06 Dec 2023 20:50:48 GMT

I have a data set "sourceTable" with RoadNumbers associated for each entry. Each entry has an Individual_Removal_Score. I want to combine all entries based on RoadNumber and SUM the Individual_Removal_Score values to create one record for each RoadNumber with a Total_Score. I then want to filter "stats" to lookup RoadNumbers that match the ID field of the target layer and return the Total_Score. This is being done as an attribute rule in pro for a field in the target dataset.

//Reference Layer and relevant fields var sourceTable = FeatureSetByName($datastore, "TNF_RoadConditionAssessment", ["RoadNumber", "Individual_Removal_Score"],false); //key field for target layer var ID = $feature.ID; //statistic variable target layer var Miles = $feature.Total_Miles; //Group reference layer by RoadNumber and SUM Individual_Removal_Scores for each entry var stats = groupby(sourceTable, 'RoadNumber', {name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'}) //Filter Summary table(stats) by RoadNumber where value is equal to the key field "ID" var Lookup = First(Filter(stats, "RoadNumber = @ID")); //return Total_Score/Miles if key field has a match return iif(Lookup == null, null, ((Lookup.Total_Score)/Miles));

Script verifies but won't save because of an error on line 13 "Attribute value lookup error". I have used this exact script without the GroupBy() function just fine so I'm at a loss. I'm assuming the GroupBy() table isn't being returned properly so there is nothing to reference? Wondering if the field "Total_Miles" needs to be created physically somewhere or if it gets created in memory while its running. The way I understand it is the GroupBy() function creates a new temp FeatureSet. I thought I was creating a FeatureSet with RoadNumber and Total_Miles (being a sum of "Individual_Removal_Score") as the fields but my thinking is that it's breaking down because there are multiple entries with the same roadnumber? (Screen shot example of the table I'm attempting to group below)

Thanks for the input!

Re: GroupBy() Function Trouble

KenBuja — Wed, 06 Dec 2023 20:51:52 GMT

If you have a field in your table that contains the mileage for each road, you would add that to the GroupBy function. Or you could use the "SHAPE__Length" field. However, since that won't be in miles, you would have to convert that in the Expression. Here, I'm making the conversion from meters to miles

var stats = groupby(sourceTable, 'RoadNumber', [ {name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'}, {name: 'Total_Miles', expression: 'SHAPE__Length * 0.000621371', statistic: 'SUM'} ] );

Then in the final line, you would use that summarized length

return iif(Lookup == null, null, ((Lookup.Total_Score)/Lookup.Total_Miles));

Re: GroupBy() Function Trouble

amrodri01 — Wed, 06 Dec 2023 21:22:44 GMT

I do have a field in the sourceTable that has the mileage of the road based on the roadnumber. I guess I'm not sure how it would display properly because the sourceTable is a point layer and the target layer is a line layer. Both layers have their own fields with the total mileage of the road (not shape length). If I were to GroupBy() the ML1_Road_Miles wouldn't that just sum the mileage and not have it a single value? Thats why I had it referencing the line FeatureSet "Total_Miles" as a variable. Maybe an average instead which would give it the correct value?

Below is a snip of the line FeatureSet. This script is writing to the "Storage_Score field

Below is a snip of the point FeatureSet "sourceTable" w/ the Road Mileage

Even still the filter function displays an error on line 7. Below is how I have it now, but it doesn't seem right.

var sourceTable = FeatureSetByName($datastore, "TNF_RoadConditionAssessment", ["RoadNumber", "Individual_Removal_Score", "ML1_Road_Miles"],false); var ID = $feature.ID; var stats = groupby(sourceTable, 'RoadNumber', [{name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'}, {name: 'Total_Miles', expression: 'ML1_Road_Miles', statistic: 'SUM'}]) var Lookup = First(Filter(stats, "RoadNumber = @ID")); return iif(Lookup == null, null, ((Lookup.Total_Score)/Lookup.Total_Miles));

Re: GroupBy() Function Trouble

KenBuja — Wed, 06 Dec 2023 21:43:58 GMT

Showing that other table makes it a little more understandable. In that case, leave off that second GroupBy expression and use your original version.

What is the error you're getting on line 7?

Re: GroupBy() Function Trouble

amrodri01 — Wed, 06 Dec 2023 23:05:25 GMT

Gotcha, okay with the original script it was referencing line 11. Just kind of weird since it only shows in the attribute rules pane and not in the expression entry window.

Re: GroupBy() Function Trouble

amrodri01 — Fri, 08 Dec 2023 17:05:42 GMT

Figured out the solution, needed to filter the multiple RoadNumber records into a table with just a single matching ID (Line 8 ) then the rest of the script functioned.

//Reference Layer and relevant fields var sourceTable = FeatureSetByName($datastore, "TNF_RoadConditionAssessment", ["RoadNumber", "Individual_Removal_Score"],false); //key field for target layer var ID = $feature.ID; //statistic variable target layer var Miles = $feature.Total_Miles; //Filter all features that equal road ID var Lookupall = Filter(sourceTable, "RoadNumber = @ID") //Group reference layer by RoadNumber and SUM Individual_Removal_Scores for each entry var stats = groupby(Lookupall,'RoadNumber', {name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'}) //Filter Summary table(stats) by RoadNumber where value is equal to the key field "ID" var Lookup = first(Filter(stats, "RoadNumber = @ID")) //return Total_Score/Miles if key field has a match return iif(Lookup == null, 0, (Lookup.Total_Score/Miles));