Filter status of most recent date (max date)

[AdrianPatrulescu]

Hi 

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

An example is presented below

Status	Date camera update	Camera ID	ObjectId
Inactive	1645715427825	CC00YU	286
Active	1643285646665	CC00YU	242
Inactive	1643278473122	CC00YU	241
Active	1642600314062	CC00YU	232
Active	1637750208558	CC00YU	196
Active	1634287288282	CC00YU	184
Active	1632730802711	CC00YU	167
Active	1631523268712	CC00YU	144
Active	1630504452114	CC00YU	126
Active	1628077793255	CC00YU	105
Inactive	1627373615862	CC00YU	96
Inactive	1624009206956	CC00YU	74
Active	1645695899246	CC010M	282
Active	1643807221100	CC010M	259
Inactive	1643804224255	CC010M	257
Active	1642666427642	CC010M	235
Inactive	1627376100791	CC010M	99
Active	1626167609040	CC010M	88
Inactive	1625648947010	CC010M	83
Active	1625141819099	CC010M	79
Active	1624270142117	CC010M	75
Active	1621412023921	CC010M	42
Active	1620202661707	CC010M	31
Inactive	1619008245666	CC010M	26

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 update	Camera ID	ObjectId
1645715427825	CC00YU	286
1645695899246	CC010M	282

and I don't know the status

If I insert 'status' in the GroupBy

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

Status	Date camera update	Camera ID	ObjectId
Inactive	1645715427825	CC00YU	286
Active	1643285646665	CC00YU	242
Active	1645695899246	CC010M	282
Inactive	1643804224255	CC010M	257

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

[AndrewPadilla]

@AdrianPatrulescu were you able to find a solution for your example?  I am facing a very similar issue. However, I would be looking for the most recent Date camera update for each unique Camera ID  and the Status of that row. I have tried to add Status in the groupBy function as below. However, this takes the alphabetical max of the Status which would always be "Inactive" for a camera that has both "Inactive" and "Active" rows. How does one retain the additional fields/columns that are in the selected rows?

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

{name: 'data', expression: 'Status', statistic: 'MAX'}]);

Thanks,

Andy

[AdrianPatrulescu]

Hi Andy,

I didn't have a solution for my problem .

Best regards

Adrian

[AndrewPadilla]

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'), <itemid>, <layernumber>, ["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]

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