ArcGIS Dashboard Arcade Group and Join Table

Anonymous User

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));

 

 

jcarlson

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}'`))