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]

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"?

[Anonymous User]

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 😕

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 · Accepted solution]

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

[Anonymous User]

Thanks so much! That last line did the trick.

[kmsmikrud]

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

var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), '669d6d77f2bc4ca7ad1ec208c3db90fd', 1, ["fishdataid","towername",'samptime','stockgroup'], false);

//return fs
var grouped = GroupBy(fs, ['fishdataid'], [
    { name: 'max_samptime', expression: "samptime", statistic: 'MAX' }]);
//return grouped


var features = [];
var feat;


for (var f in grouped){
    var fish = f["fishdataid"]
    var sdate = ToUTC(f["max_samptime"])
    var sql = "samptime = '" + sdate + "'AND fishdataid = '" + fish + "'"
    for (var p in Filter(fs, sql)){
        feat = {
            attributes: {
                fishdataid: fish,
                //max_samptime: sdate,
                towername: p['towername'],
                stockgroup: p['stockgroup'],
            }
        }
    
    Push(features,feat)
    }
}

var joinedDict = {
    fields: [
        {name: "fishdataid", type: "esriFieldTypeInteger"},
        {name: "max_samptime", type: "esriFieldTypeDate"},
        {name: "towername", type: "esriFieldTypeString"},
        {name: "stockgroup", type: "esriFieldTypeString"},   
    ],
    'geometryType': '',
    'features': features
};

return FeatureSet(Text(joinedDict));