https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-groupby-not-grouping/m-p/1486509#M9769 <P>Hello,</P><P>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.</P><P>So, if the original table looks like this:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="50%"><STRONG>Type</STRONG></TD><TD width="50%"><STRONG>Year</STRONG></TD></TR><TR><TD width="50%">Apple</TD><TD width="50%">2022</TD></TR><TR><TD width="50%">Apple</TD><TD width="50%">2022</TD></TR><TR><TD width="50%">Banana</TD><TD width="50%">2023</TD></TR><TR><TD width="50%">Pear</TD><TD width="50%">2024</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I'm trying to create a table that looks like this:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="25%"><STRONG>Type</STRONG></TD><TD width="25%"><STRONG>2022</STRONG></TD><TD width="25%"><STRONG>2023</STRONG></TD><TD width="25%"><STRONG>2024</STRONG></TD></TR><TR><TD width="25%">Apple</TD><TD width="25%">2</TD><TD width="25%">0</TD><TD width="25%">0</TD></TR><TR><TD width="25%">Banana</TD><TD width="25%">0</TD><TD width="25%">1</TD><TD width="25%">0</TD></TR><TR><TD width="25%">Pear</TD><TD width="25%">0</TD><TD width="25%">0</TD><TD width="25%">1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="25%"><STRONG>Type</STRONG></TD><TD width="25%"><STRONG>2022</STRONG></TD><TD width="25%"><STRONG>2023</STRONG></TD><TD width="25%"><STRONG>2024</STRONG></TD></TR><TR><TD width="25%">Apple</TD><TD width="25%">1</TD><TD width="25%">null</TD><TD width="25%">null</TD></TR><TR><TD width="25%">Apple</TD><TD width="25%">1</TD><TD width="25%">null</TD><TD width="25%">null</TD></TR><TR><TD width="25%">Banana</TD><TD width="25%">null</TD><TD width="25%">1</TD><TD width="25%">null</TD></TR><TR><TD width="25%">Pear</TD><TD width="25%">null</TD><TD width="25%">null</TD><TD width="25%">1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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!</P><LI-CODE lang="python">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' }]);</LI-CODE> Thu, 06 Jun 2024 18:26:30 GMT frasertab 2024-06-06T18:26:30Z https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-groupby-not-grouping/m-p/1486509#M9769 <P>Hello,</P><P>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.</P><P>So, if the original table looks like this:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="50%"><STRONG>Type</STRONG></TD><TD width="50%"><STRONG>Year</STRONG></TD></TR><TR><TD width="50%">Apple</TD><TD width="50%">2022</TD></TR><TR><TD width="50%">Apple</TD><TD width="50%">2022</TD></TR><TR><TD width="50%">Banana</TD><TD width="50%">2023</TD></TR><TR><TD width="50%">Pear</TD><TD width="50%">2024</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I'm trying to create a table that looks like this:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="25%"><STRONG>Type</STRONG></TD><TD width="25%"><STRONG>2022</STRONG></TD><TD width="25%"><STRONG>2023</STRONG></TD><TD width="25%"><STRONG>2024</STRONG></TD></TR><TR><TD width="25%">Apple</TD><TD width="25%">2</TD><TD width="25%">0</TD><TD width="25%">0</TD></TR><TR><TD width="25%">Banana</TD><TD width="25%">0</TD><TD width="25%">1</TD><TD width="25%">0</TD></TR><TR><TD width="25%">Pear</TD><TD width="25%">0</TD><TD width="25%">0</TD><TD width="25%">1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="25%"><STRONG>Type</STRONG></TD><TD width="25%"><STRONG>2022</STRONG></TD><TD width="25%"><STRONG>2023</STRONG></TD><TD width="25%"><STRONG>2024</STRONG></TD></TR><TR><TD width="25%">Apple</TD><TD width="25%">1</TD><TD width="25%">null</TD><TD width="25%">null</TD></TR><TR><TD width="25%">Apple</TD><TD width="25%">1</TD><TD width="25%">null</TD><TD width="25%">null</TD></TR><TR><TD width="25%">Banana</TD><TD width="25%">null</TD><TD width="25%">1</TD><TD width="25%">null</TD></TR><TR><TD width="25%">Pear</TD><TD width="25%">null</TD><TD width="25%">null</TD><TD width="25%">1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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!</P><LI-CODE lang="python">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' }]);</LI-CODE> Thu, 06 Jun 2024 18:26:30 GMT https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-groupby-not-grouping/m-p/1486509#M9769 frasertab 2024-06-06T18:26:30Z https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-groupby-not-grouping/m-p/1486550#M9770 <P>Your return at line 33 means the GroupBy function didn't run.</P><P>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.</P><LI-CODE lang="javascript">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' }]);</LI-CODE><P>&nbsp;</P><P>&nbsp;</P> Thu, 06 Jun 2024 19:49:55 GMT https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-groupby-not-grouping/m-p/1486550#M9770 KenBuja 2024-06-06T19:49:55Z https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-groupby-not-grouping/m-p/1486620#M9772 <P>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!</P> Thu, 06 Jun 2024 21:00:47 GMT https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-groupby-not-grouping/m-p/1486620#M9772 frasertab 2024-06-06T21:00:47Z