Convert Text to Number Using Arcade and Get Average in ArcGIS Dashboards

GeodataSystems — Tue, 28 Feb 2023 10:28:37 GMT

Hi! I have a training evaluation survey form that gets feedback and returns 5,4,3,2,1. For every item that they need to rate, Survey123 returns strings (5,4,3,2,1 in text format). I need to convert this to numeric so that I can get the average for every item and then get the average for each category. Thank you!

Re: Convert Text to Number Using Arcade and Get Average in ArcGIS Dashboards

JohannesLindner — Tue, 28 Feb 2023 13:09:42 GMT

To get the average of a featureset column , we can use the Average() function.

Let's load some test data:

// create a test fs for a scoring survey. // 3 text fields with scores between 1 and 5 var survey_dict = { fields:[ {name: "Score1", type: "esriFieldTypeString"}, {name: "Score2", type: "esriFieldTypeString"}, {name: "Score3", type: "esriFieldTypeString"}, ], geometryType: "", features: [] } for(var i = 0; i < 200; i++) { var rating = {attributes: { Score1: Text(Round(4 * Random() + 1)), Score2: Text(Round(4 * Random() + 1)), Score3: Text(Round(4 * Random() + 1)), }} Push(survey_dict.features, rating) } var survey = Featureset(Text(survey_dict)) // return survey

Now, if we take the Average() of column Score1, we get this result:

This is obviously incorrect. It seems like the function doesn't convert to Number(), but instead somehow uses the bytes of the string values as number (or something else...).

So we need to covert that column to a proper integer column first. I haven't found a way to do that inside the Average() function, so we should probably just convert the featureset before calling Average():

var score_fields = ["Score1", "Score2", "Score3"] // create an empty featureset dict var numeric_survey_dict = { fields: [], geometryType: "", features: [] } // add the score fields as integer fields for(var i in score_fields) { var field = {name: score_fields[i], type: "esriFieldTypeInteger"} Push(numeric_survey_dict.fields, field) } // go thorugh the original featureset and convert each value to Number() for(var f in survey) { var converted_f = Dictionary() for(var i in score_fields) { var field = score_fields[i] converted_f[field] = Number(f[field]) } Push(numeric_survey_dict.features, {attributes: converted_f}) } // convert the dictionary to a featureset var numeric_survey = Featureset(Text(numeric_survey_dict)) // get the Average() return Average(numeric_survey, "Score1")

topic Convert Text to Number Using Arcade and Get Average in ArcGIS Dashboards in ArcGIS Dashboards Questions

Convert Text to Number Using Arcade and Get Average in ArcGIS Dashboards

Re: Convert Text to Number Using Arcade and Get Average in ArcGIS Dashboards