topic ArcGIS Dashboard Arcade Group and Join Table in ArcGIS Dashboards Questions

ArcGIS Dashboard Arcade Group and Join Table

Anonymous User — Thu, 28 Jul 2022 15:36:43 GMT

Hello! I have a table in AGOL. I want to group it in two different ways and then join those 2 tables together to calculate a percent for a dashboard. When I test it, I keep getting the general "Execution Error:Error". Any idea what I'm doing wrong here?

// Write an expression that returns a FeatureSet. // Documentation: https://arcg.is/3c419TD // Samples: https://arcg.is/38SEWWz var portal = Portal('https://phl.maps.arcgis.com'); var fs = FeatureSetByPortalItem( portal, '4de29dcdbea84087842717a6143a68fd', 0, [ 'DATE_FILTER', 'POSITION_TYPE', 'RACE_ETHNICITY', 'HIRING_DEPARTMENT', 'TOTAL_COUNT' ], false ); var race_total = GroupBy(fs, [ {name: 'DATE_FILTER', expression: 'DATE_FILTER' }, {name: 'RACE_ETHNICITY', expressions: 'RACE_ETHNICITY'} ], [{ name:'TOTAL_RACE', expression:'TOTAL_COUNT', statistic:'SUM'}] ); var total = GroupBy(fs, ['DATE_FILTER'], [{ name:'TOTAL_EMP', expression:'TOTAL_COUNT', statistic:'SUM'}] ); // Create empty features array and feat object var features = []; var feat; // Populate Feature Array for (var r in race_total) { var tableID = r["DATE_FILTER"] for (var t in Filter(total, "DATE_FILTER = "+tableID)){ feat = { attributes: { DATE_FILTER: tableID, RACE_ETHNICITY: r["RACE_ETHNICITY"], TOTAL_RACE: r["TOTAL_RACE"], TOTAL_EMP: t["TOTAL_EMP"], PER_RACE: Round((r["TOTAL_RACE"])/(t["TOTAL_EMP"])*100,2), } } Push(features, feat) } } var joinedDict = { fields: [ { name: "DATE_FILTER", type: "esriFieldTypeString" }, { name: "RACE_ETHNICITY", type: "esriFieldTypeString" }, { name: "TOTAL_RACE", type: "esriFieldTypeDouble" }, { name: "TOTAL_EMP", type: "esriFieldTypeDouble" }, { name: "PER_RACE", type: "esriFieldTypeDouble" }, ], 'geometryType': '', 'features':features }; // Return dictionary cast as a feature set return FeatureSet(Text(joinedDict));

Re: ArcGIS Dashboard Arcade Group and Join Table

jcarlson — Thu, 28 Jul 2022 15:53:47 GMT

I think you want your Push statement to be inside of the of for (var t... block, not outside of it. Otherwise it's only going to push feat into the array once for each loop of the for (var r... block. Unless that's what you want?

Also, what kind of field is "DATE_FILTER"?

Re: ArcGIS Dashboard Arcade Group and Join Table

Anonymous User — Thu, 28 Jul 2022 16:07:52 GMT

Thanks Josh! I switched the Push statement, but that did not work (see code below). I had the Push in the original spot only because that is where the example had it. I received the same error.

DATE_FILTER is a string, formatted YYYY-MM

Any other ideas? I'm pretty new to Arcade so this is a struggle 😕

Re: ArcGIS Dashboard Arcade Group and Join Table

jcarlson — Thu, 28 Jul 2022 16:50:06 GMT

This would be a great time to test out Console statements. By using Console('some text'), you can print informational messages to the console, which is super helpful for identifying where a script is going wrong.

I added a few to your expression, and I can see that my console messages stop appearing in the first loop starting in line 40. It appears to be related to your filter statement.

Looking at this a bit closer, you're really just trying to pull the total out, and your filtered FeatureSet is probably a single row. In that case, you can just use First, then access the attribute directly, and not bother with a nested for loop.

Also, check out the use of template literals to pipe variables into a string without needing to use concatenation.

All that aside, I think the issue here is actually the data itself. When I try to test this, I can see the server returning an error message in the console, and it lists this as the query string:

" (DATE_FILTER = (2019 - 5) ) "

Are your values formatted with spaces and parentheses like that? I could see the server interpreting that as a mathematical statement, and it's trying to filter based on the results, i.e., 2014, in the example above.

Try putting single quotes around your tableID in the expression so that the server knows it's text, not a formula. When I do that, I can see that the server is working through the featureset, sending repeated queries to the server, and the error messages go away. Seems to take a while to run, though, but it works.

To wrap this up, use this line the replace your line 42, then remove the closing curly bracket on line 52 that used to define the for (var t... loop. Lines 43 - 51 can remain as there are.

var t = First(Filter(total, `DATE_FILTER = '${tableID}'`))

Re: ArcGIS Dashboard Arcade Group and Join Table

Anonymous User — Thu, 28 Jul 2022 18:07:44 GMT

Thanks so much! That last line did the trick.

Re: ArcGIS Dashboard Arcade Group and Join Table

kmsmikrud — Fri, 09 Sep 2022 19:21:34 GMT

Hello!,

I have a very similar data expression using the same join example that 'almost' works except I cannot get the max_samptime attribute to populate in the result Feature Dataset although I believe the other data is accurate. At the moment I have it commented out in the code included and have tried several renditions, but I just can not figure it out. I would so appreciate your thoughts! Thanks, Kathy