Select to view content in your preferred language

Arcade: Only Return Most Recent/Latest Record For Each Day?

1326
6
Jump to solution
05-18-2023 03:09 PM
ArmstKP
Regular Contributor

I have a hosted feature layer with multiple records per day for a feature.  How would I return only the most recent record per day?

Here is my code, but returns multiple records per day:

 

var mowhistory = Overlaps(FeatureSetByPortalItem(Portal('https://www.arcgis.com'),"00871a3866be4ceaa11f7841b43cf82a",0), $feature);

var mowhistorytimeSorted = Orderby(mowhistory, 'lastserviced DSC')
var popupString = "";

for (var myfeature in mowhistorytimeSorted){
    popupString += text(myfeature.lastserviced, 'MMM DD Y, hh:mm A') +
    TextFormatting.NewLine
}

return popupString
0 Kudos
1 Solution

Accepted Solutions
JustinReynolds
Regular Contributor

Here is an example that gives you both.  You won't need to change much.

You will need to change:

  • $layer on line 2 to your FeatureSetByPortalItem
  • service_date on line 2 to lastserviced
  • .service_date on line 11 to .lastserviced
  • .service_date on line 24 to .lastserviced

Decide whether you want firstFeature or lastFeature on line 22

 

// Sort ascending is important
var fset = OrderBy($layer, 'service_date ASC');

var currentDateGroup;
var _date;
var firstFeatures = [];
var lastFeatures = [];
var popupString;

for (var f in fset) {
  _date = Text(f.service_date, 'YMMDD');
  if (currentDateGroup != _date) {
    currentDateGroup = _date;
    Push(firstFeatures, f);
    Push(lastFeatures, f);
  } else {
    Pop(lastFeatures);
    Push(lastFeatures, f);
  };
};

var features = lastFeatures; // choose to use firstFeatures, or last features here
for (var fIdx in features) {
  popupString += Text(features[fIdx].service_date, 'MMM DD Y, hh:mm A') + TextFormatting.NewLine;
};
return popupString;

 

Output

 

Newest feature for each day based on datetime field (aka lastFeature)
Feb 28 2023, 05:01 PM
Mar 01 2023, 03:59 PM
Mar 02 2023, 05:03 PM
Mar 08 2023, 04:58 PM
Mar 10 2023, 08:51 AM

Oldest feature for each day based on datetime field(aka firstFeature)
Feb 28 2023, 01:13 PM
Mar 01 2023, 08:13 AM
Mar 02 2023, 10:09 AM
Mar 08 2023, 09:22 AM
Mar 10 2023, 07:36 AM

 

- Justin Reynolds, PE

View solution in original post

6 Replies
JustinReynolds
Regular Contributor

Hello,

You can do away with the for loop, it is not needed to get first or last record by date.

 

var dateOfInterest = Today()

// Get First Record by Datetime
var mowhistorytimeSorted = First(Filter(OrderBy(mowhistory, 'lastserviced ASC'), 'lastserviced = @dateOfInterest'))

// Get Last Record by Datetime
var mowhistorytimeSorted = First(Filter(OrderBy(mowhistory, 'lastserviced DESC'), 'lastserviced = @dateOfInterest'))

 

- Justin Reynolds, PE
0 Kudos
ArmstKP
Regular Contributor

@JustinReynolds Thank you for your feedback!  I am actually looking to get a record back for every day there is a record, but only the last/most recent.  ie:

Dec 2 2022 2:30 PM

Dec 1 2022 2:45 PM

Nov 30 2022 4:29 PM

0 Kudos
JustinReynolds
Regular Contributor

Here is an example that gives you both.  You won't need to change much.

You will need to change:

  • $layer on line 2 to your FeatureSetByPortalItem
  • service_date on line 2 to lastserviced
  • .service_date on line 11 to .lastserviced
  • .service_date on line 24 to .lastserviced

Decide whether you want firstFeature or lastFeature on line 22

 

// Sort ascending is important
var fset = OrderBy($layer, 'service_date ASC');

var currentDateGroup;
var _date;
var firstFeatures = [];
var lastFeatures = [];
var popupString;

for (var f in fset) {
  _date = Text(f.service_date, 'YMMDD');
  if (currentDateGroup != _date) {
    currentDateGroup = _date;
    Push(firstFeatures, f);
    Push(lastFeatures, f);
  } else {
    Pop(lastFeatures);
    Push(lastFeatures, f);
  };
};

var features = lastFeatures; // choose to use firstFeatures, or last features here
for (var fIdx in features) {
  popupString += Text(features[fIdx].service_date, 'MMM DD Y, hh:mm A') + TextFormatting.NewLine;
};
return popupString;

 

Output

 

Newest feature for each day based on datetime field (aka lastFeature)
Feb 28 2023, 05:01 PM
Mar 01 2023, 03:59 PM
Mar 02 2023, 05:03 PM
Mar 08 2023, 04:58 PM
Mar 10 2023, 08:51 AM

Oldest feature for each day based on datetime field(aka firstFeature)
Feb 28 2023, 01:13 PM
Mar 01 2023, 08:13 AM
Mar 02 2023, 10:09 AM
Mar 08 2023, 09:22 AM
Mar 10 2023, 07:36 AM

 

- Justin Reynolds, PE
ArmstKP
Regular Contributor

@JustinReynolds Bingo.  This worked like a charm.  Much appreciated, thank you!

Here is my final and functioning code:

 

 

var mowhistory = Overlaps(FeatureSetByPortalItem(Portal('https://www.arcgis.com'),"00871a3866be4ceaa11f7841b43cf82a",0), $feature);
var fset = OrderBy(mowhistory, 'lastserviced DSC');

var currentDateGroup;
var _date;
var firstFeatures = [];
var lastFeatures = [];
var popupString;

for (var f in fset) {
  _date = Text(f.lastserviced, 'YMMDD');
  if (currentDateGroup != _date) {
    currentDateGroup = _date;
    Push(firstFeatures, f);
    Push(lastFeatures, f);
  } else {
    Pop(lastFeatures);
    Push(lastFeatures, f);
  };
};

var features = lastFeatures; // choose to use firstFeatures, or last features here
for (var fIdx in features) {
  popupString += Text(features[fIdx].lastserviced, 'MMM DD Y, hh:mm A') + TextFormatting.NewLine;
};
return popupString;

 

0 Kudos
KenBuja
MVP Esteemed Contributor

Use First to select the first item from your sorted list

 

var myfeature = First(Orderby(mowhistory, 'lastserviced DSC'));
return text(myfeature.lastserviced, 'MMM DD Y, hh:mm A')

 

 

0 Kudos
ArmstKP
Regular Contributor

@KenBuja 

I am actually looking to get a record back for every day there is a record, but only the last/most recent.  ie:

Dec 2 2022 2:30 PM

Dec 1 2022 2:45 PM

Nov 30 2022 4:29 PM