Filter status of most recent date (max date)

Question

Hi

I tried to filter a feature set in order to extract status of the most recent record

An example is presented below

StatusDate camera updateCamera IDObjectIdInactive1645715427825CC00YU286Active1643285646665CC00YU242Inactive1643278473122CC00YU241Active1642600314062CC00YU232Active1637750208558CC00YU196Active1634287288282CC00YU184Active1632730802711CC00YU167Active1631523268712CC00YU144Active1630504452114CC00YU126Active1628077793255CC00YU105Inactive1627373615862CC00YU96Inactive1624009206956CC00YU74Active1645695899246CC010M282Active1643807221100CC010M259Inactive1643804224255CC010M257Active1642666427642CC010M235Inactive1627376100791CC010M99Active1626167609040CC010M88Inactive1625648947010CC010M83Active1625141819099CC010M79Active1624270142117CC010M75Active1621412023921CC010M42Active1620202661707CC010M31Inactive1619008245666CC010M26

I need to answer the quiestion: How many active camere do I have?

The answer for the above example is 1 (because camera ID:CC010M has the latest status active - colored green and the camera ID: CC00YU has the latest status inactive - colored blue)

I need to Filter/GroupBy the above table to obtain one table with 2 rows (objectID 286 and 282)

I was trying with GroupBy but the result omits status:

GroupBy(fs, ['camID',],
[{name: 'data', expression: 'DateTimeCamUpdate', statistic: 'MAX'}]);

Date camera updateCamera IDObjectId1645715427825CC00YU2861645695899246CC010M282

and I don't know the status

If I insert 'status' in the GroupBy

GroupBy(fs, ['camID','status'],
[{name: 'data', expression: 'DateTimeCamUpdate', statistic: 'MAX'}]);

StatusDate camera updateCamera IDObjectIdInactive1645715427825CC00YU286Active1643285646665CC00YU242Active1645695899246CC010M282Inactive1643804224255CC010M257

I have latest status of each inactive/active status

My goal is to find out which is the latest (max date) status of an unique camera.

Thank you

Adrian

AdrianPatrulescu · Answer

Hi @AndrewPadilla

Thank you for your interest in this topic

I tried to adapt the script to my featureset:

var fs = FeatureSetByPortalItem( Portal('https://www.arcgis.com'), '22b55eb9ce55450a8008251d6f0b48cc', 0, ['status','camID','DateTimeCamUpdate'], false );
//return fs
var grouped = GroupBy(fs, ['camID'], [{ name: 'max_time', expression: "DateTimeCamUpdate", statistic: 'MAX' }]);
//return grouped
var features = [];
var feat;

for (var f in grouped){
var cam = f["camID"]
var sdate = ToUTC(f["max_time"])
var sdate_local = f["max_time"]
var sql = "DateTimeCamUpdate = '" + sdate + "'AND camID = '" + cam + "'"
for (var p in Filter(fs, sql)){
feat = {
attributes: {
camID: cam,
max_time: sdate,
status: p['status'],
}
}

Push(features,feat)
}
}

var joinedDict = {
fields: [
{name: "camID", type: "esriFieldTypeString"},
{name: "max_time", type: "esriFieldTypeString"},
{name: "status", type: "esriFieldTypeString"},

],
'geometryType': '',
'features': features
};

return FeatureSet(Text(joinedDict));

But the result is an empty dataset

May be is an error (if you have time to look at it)

Best regards

Adrian

AndrewPadilla · Answer

Hi @AdrianPatrulescu, I did end up finding a solution to use in my dashboard thanks to @KathySmikrud. It will group the fields you bring in from your feature service by an id (fishdataid), get max(time) and all other columns for that id at the max(time). Let me know if works for you. var fs = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), , , ["fishdataid","col1","col2", "samptime"], false); var grouped = GroupBy(fs, ['fishdataid'], [ { name: 'max_samptime', expression: "samptime", statistic: 'MAX' }]); //return grouped var features = []; var feat; for (var f in grouped){ var fish = f["fishdataid"] var sdate = ToUTC(f["max_samptime"]) var sdate_local = f["max_samptime"] var sql = "samptime = '" + sdate + "'AND fishdataid = '" + fish + "'" for (var p in Filter(fs, sql)){ feat = { attributes: { fishdataid: fish, max_samptime: sdate, col1: p['col1'], col 2: p['col2'], } } Push(features,feat) } } var joinedDict = { fields: [ {name: "fishdataid", type: "esriFieldTypeInteger"}, {name: "max_samptime", type: "esriFieldTypeString"}, {name: "col1", type: "esriFieldTypeString"}, {name: "col2", type: "esriFieldTypeInteger"}, ], 'geometryType': '', 'features': features }; return FeatureSet(Text(joinedDict));

AdrianPatrulescu · Answer

Hi Andy,

I didn't have a solution for my problem .

Best regards

Adrian