Select to view content in your preferred language

Filter featureset in data expression using DateDiff

202
2
Jump to solution
4 weeks ago
Labels (1)
KateBray
New Contributor II

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. 

0 Kudos
1 Solution

Accepted Solutions
KenBuja
MVP Esteemed Contributor

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

 

 

View solution in original post

2 Replies
KenBuja
MVP Esteemed Contributor

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

 

 

KateBray
New Contributor II

Thanks @KenBuja works perfectly and is a good suggestion for a work around!

Additionally I wasnt aware of the performance implications of filtering on featuresets so the added performance suggestion I will incorporate with further scripts.  Thank you for taking the time to share - I really appreciate it!

0 Kudos