Arcade Data Expression GroupBy not grouping

[frasertab]

Hello,

I have been trying to create a data expression for an AGOL dashboard that turns the values from a column ('Year') into their own columns, and then groups by another column ('Type') so that I have the counts for each year for that type.

So, if the original table looks like this:

Type	Year
Apple	2022
Apple	2022
Banana	2023
Pear	2024

I'm trying to create a table that looks like this:

Type	2022	2023	2024
Apple	2	0	0
Banana	0	1	0
Pear	0	0	1

I'm not new to scripting but I am new to Arcade, so I've been looking around and piecing together samples to get what I need, and I think I've almost got it. The code below structures the table the way I want it with the columns, but it's not grouping by Type the way I want it to. It's outputting the equivalent of this:

Type	2022	2023	2024
Apple	1	null	null
Apple	1	null	null
Banana	null	1	null
Pear	null	null	1

Can anyone let me know where I'm going wrong in my GroupBy expression? Or is there a larger problem with my approach. Thank you!

var p = Portal('url');

var fs = FeatureSetByPortalItem(
    p,
    'id',
    0,
    [
        'Original_Year_Field',
        'Original_Type_Field'
    ],
    false
);

var yearDict = {'fields': [{'name':'Type', 'type':'esriFieldTypeString'},
                           {'name':'2022', 'type':'esriFieldTypeInteger'},
                           {'name':'2023', 'type':'esriFieldTypeInteger'},
                           {'name':'2024', 'type':'esriFieldTypeInteger'}],
                'geometryType': '',
                'features': []};

for (var feature in fs) {
    var cat = feature['Original_Type_Field']
    var yearone = Iif(feature['Original_Year_Field'] == 2022,1,null)
    var yeartwo = Iif(feature['Original_Year_Field'] == 2023,1,null)
    var yearthree = Iif(feature['Original_Year_Field'] == 2024,1,null)
    Push(yearDict.features, {"attributes": {"Type": cat,
                                            "2022": yearone,
                                            "2023": yeartwo,
                                            "2024": yearthree}})
}

var fs_dict = FeatureSet(Text(yearDict));
return fs_dict

return GroupBy(fs_dict, ['Type'],
       [{ name: '2022', expression: '2022', statistic: 'COUNT' },
        { name: '2023', expression: '2023', statistic: 'COUNT' },
        { name: '2024', expression: '2024', statistic: 'COUNT' }]);

[KenBuja · Accepted solution]

Your return at line 33 means the GroupBy function didn't run.

This sample script gives the expected table. One thing I had to change to make it work properly is to have the fields in the Dictionary start with a string.

var fs = FeatureSet(
  {"fields":[
      {"alias":"Type","name":"Type","type":"esriFieldTypeString"},
      {"alias":"Year","name":"Year","type":"esriFieldTypeInteger"}],
      "spatialReference":{"wkid":4326},
      "geometryType":"",
      "features":[
        {"geometry":'', "attributes":{"Type":'Apple',"Year":2022}},
        {"geometry":'', "attributes":{"Type":'Apple',"Year":2022}},
        {"geometry":'', "attributes":{"Type":'Banana',"Year":2023}},
        {"geometry":'', "attributes":{"Type":'Pear',"Year":2024}},
    ]
  }
);

var yearDict = {'fields': [{'name':'Type', 'type':'esriFieldTypeString'},
                           {'name':'y2022', 'type':'esriFieldTypeInteger'},
                           {'name':'y2023', 'type':'esriFieldTypeInteger'},
                           {'name':'y2024', 'type':'esriFieldTypeInteger'}],
                'geometryType': '',
                'features': []};

for (var feature in fs) {
    var cat = feature['Type']
    var yearone = Iif(feature['Year'] == 2022,1,null)
    var yeartwo = Iif(feature['Year'] == 2023,1,null)
    var yearthree = Iif(feature['Year'] == 2024,1,null)
    Push(yearDict.features, {"attributes": {"Type": cat,
                                            "y2022": yearone,
                                            "y2023": yeartwo,
                                            "y2024": yearthree}})
}

var fs_dict = FeatureSet(Text(yearDict));
//return fs_dict

return GroupBy(fs_dict, ['Type'],
       [{ name: '2022', expression: 'y2022', statistic: 'Count' },
        { name: '2023', expression: 'y2023', statistic: 'Count' },
        { name: '2024', expression: 'y2024', statistic: 'Count' }]);

[frasertab]

Thank you! I figured I was probably down to some syntax errors, but you've saved me hours of tinkering with it. I have no idea how long it would have taken me to realize not having a letter in the field name was causing issues. Thanks again!