Filter status of most recent date (max date)

884
4
03-21-2022 07:51 AM
Labels (1)
AdrianPatrulescu
New Contributor III

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 IDObjectId
Inactive1645715427825CC00YU286
Active1643285646665CC00YU242
Inactive1643278473122CC00YU241
Active1642600314062CC00YU232
Active1637750208558CC00YU196
Active1634287288282CC00YU184
Active1632730802711CC00YU167
Active1631523268712CC00YU144
Active1630504452114CC00YU126
Active1628077793255CC00YU105
Inactive1627373615862CC00YU96
Inactive1624009206956CC00YU74
Active1645695899246CC010M282
Active1643807221100CC010M259
Inactive1643804224255CC010M257
Active1642666427642CC010M235
Inactive1627376100791CC010M99
Active1626167609040CC010M88
Inactive1625648947010CC010M83
Active1625141819099CC010M79
Active1624270142117CC010M75
Active1621412023921CC010M42
Active1620202661707CC010M31
Inactive1619008245666CC010M26

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 IDObjectId
1645715427825CC00YU286
1645695899246CC010M282

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 IDObjectId
Inactive1645715427825CC00YU286
Active1643285646665CC00YU242
Active1645695899246CC010M282
Inactive1643804224255CC010M257

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

0 Kudos
4 Replies
AndrewPadilla
Occasional Contributor

@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

0 Kudos
AdrianPatrulescu
New Contributor III

Hi Andy,

I didn't have a solution for my problem .

Best regards

Adrian

0 Kudos
AndrewPadilla
Occasional Contributor

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

0 Kudos
AdrianPatrulescu
New Contributor III

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

0 Kudos