https://community.esri.com/t5/arcgis-dashboards-questions/filter-featureset-in-data-expression-using/m-p/1476789#M9621 <P>Looking for the right syntax to filter a featureset for the latest date after grouping by Name.&nbsp; The data comes from survey123, and is being used in a dashboard data expression.</P><LI-CODE lang="c">var Myportal = Portal('https://arcgis.com/'); var fs = FeatureSetByPortalItem( Myportal, ); var fs_gp = GroupBy(fs, ['Name'], [{name: 'recentDate', expression: 'CreationDate', statistic: 'MAX' }]); //************************ // Create empty dictionary var dict = { fields: [{name: 'Name', type: 'esriFieldTypeString'}, {name: 'Company, type: 'esriFieldTypeString'}, {name: 'Email', type: 'esriFieldTypeString'}, {name: 'CreationDate', type: 'esriFieldTypeDate'}], geometryType: "", features: [], }; // Loop through processed feature set and store its attributes var i = 0; for (var s in fs_gp) { var names = s["Name"]; var recent = date(s["recentDate"]) for (var d in Filter(fs, "Stakeholder_Name = @stakeholder AND DATEDIFF(S, CreationDate, @recent)= 0")){ dict.features[i++] = { attributes: { Name: names, Company: d["Company "], Village: d["Village"], Email: d["Email"], CreationDate: d["CreationDate"] } } } } return FeatureSet(Text(dict));</LI-CODE><P>I believe the issue is in the filter statement.&nbsp; Assistance would be greatly appreciated, thank you.&nbsp;</P> Wed, 22 May 2024 08:41:08 GMT KateBray 2024-05-22T08:41:08Z https://community.esri.com/t5/arcgis-dashboards-questions/filter-featureset-in-data-expression-using/m-p/1476789#M9621 <P>Looking for the right syntax to filter a featureset for the latest date after grouping by Name.&nbsp; The data comes from survey123, and is being used in a dashboard data expression.</P><LI-CODE lang="c">var Myportal = Portal('https://arcgis.com/'); var fs = FeatureSetByPortalItem( Myportal, ); var fs_gp = GroupBy(fs, ['Name'], [{name: 'recentDate', expression: 'CreationDate', statistic: 'MAX' }]); //************************ // Create empty dictionary var dict = { fields: [{name: 'Name', type: 'esriFieldTypeString'}, {name: 'Company, type: 'esriFieldTypeString'}, {name: 'Email', type: 'esriFieldTypeString'}, {name: 'CreationDate', type: 'esriFieldTypeDate'}], geometryType: "", features: [], }; // Loop through processed feature set and store its attributes var i = 0; for (var s in fs_gp) { var names = s["Name"]; var recent = date(s["recentDate"]) for (var d in Filter(fs, "Stakeholder_Name = @stakeholder AND DATEDIFF(S, CreationDate, @recent)= 0")){ dict.features[i++] = { attributes: { Name: names, Company: d["Company "], Village: d["Village"], Email: d["Email"], CreationDate: d["CreationDate"] } } } } return FeatureSet(Text(dict));</LI-CODE><P>I believe the issue is in the filter statement.&nbsp; Assistance would be greatly appreciated, thank you.&nbsp;</P> Wed, 22 May 2024 08:41:08 GMT https://community.esri.com/t5/arcgis-dashboards-questions/filter-featureset-in-data-expression-using/m-p/1476789#M9621 KateBray 2024-05-22T08:41:08Z https://community.esri.com/t5/arcgis-dashboards-questions/filter-featureset-in-data-expression-using/m-p/1477051#M9624 <P>I've always had difficulties in working with date functions in the Filter's sql statement. The workaround I came up with (working with some of my own data) is to use the OrderBy function to sort by the FeatureSet by the state and date and use the Distinct function to get the unique states. I then loop through the distinct states to filter the sorted list and use the first entry. This also uses <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/363906">@jcarlson</a>'s <A href="https://community.esri.com/t5/arcgis-online-blog/improving-expression-performance-a-custom-function/ba-p/1288785/jump-to/first-unread-message" target="_self">Memorize</A> function, since lots of Filter calls will slow down your script.</P><LI-CODE lang="javascript">function Memorize(fs) { var temp_dict = { fields: Schema(fs)['fields'], geometryType: '', features: [] } for (var f in fs) { var attrs = {} for (var attr in f) { attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr]) } Push( temp_dict['features'], {attributes: attrs} ) } return FeatureSet(Text(temp_dict)) } var fs = FeatureSetByPortalItem( Portal('https://noaa.maps.arcgis.com'), 'b9c527e0cb6d4c7fac39981f966fdd65', 0, ['STATE', "BEGIN_DATE", 'Station_ID'], false ); //var fs_gp = GroupBy(fs, ['STATE'], [{name: 'recentDate', expression: 'BEGIN_DATE', statistic: 'MAX' }]); var orderedFS = Memorize(OrderBy(fs, "STATE ASC, BEGIN_DATE DESC")) var distinctStates = Distinct(fs, ["STATE"]); var theDict = { fields: [{name: 'Name', type: 'esriFieldTypeString'}, {name: 'Station', type: 'esriFieldTypeString'}, {name: 'BEGIN_DATE', type: 'esriFieldTypeDate'}], geometryType: "", features: [], }; var i = 0; for (var s in distinctStates) { var state = s.STATE; if (!IsEmpty(state)) { var f = First(Filter(orderedFS, 'STATE = @state')) theDict.features[i++] = { attributes: { Name: state, Station: f.Station_ID, BEGIN_DATE: f.Begin_Date } } } } return FeatureSet(Text(theDict));</LI-CODE><P>&nbsp;</P><P>&nbsp;</P> Wed, 22 May 2024 16:13:57 GMT https://community.esri.com/t5/arcgis-dashboards-questions/filter-featureset-in-data-expression-using/m-p/1477051#M9624 KenBuja 2024-05-22T16:13:57Z https://community.esri.com/t5/arcgis-dashboards-questions/filter-featureset-in-data-expression-using/m-p/1478206#M9649 <P>Thanks&nbsp;<a href="https://community.esri.com/t5/user/viewprofilepage/user-id/2839">@KenBuja</a>&nbsp;works perfectly and is a good suggestion for a work around!</P><P>Additionally I wasnt aware of the performance implications of filtering on featuresets so the added performance suggestion I will incorporate with further scripts.&nbsp; Thank you for taking the time to share - I really appreciate it!</P> Fri, 24 May 2024 08:17:24 GMT https://community.esri.com/t5/arcgis-dashboards-questions/filter-featureset-in-data-expression-using/m-p/1478206#M9649 KateBray 2024-05-24T08:17:24Z