Filter featureset in data expression using DateDiff

KateBray

Looking for the right syntax to filter a featureset for the latest date after grouping by Name.  The data comes from survey123, and is being used in a dashboard data expression.

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

I believe the issue is in the filter statement.  Assistance would be greatly appreciated, thank you. 

kenbuja

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 @jcarlson's Memorize function, since lots of Filter calls will slow down your script.

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