Arcade Data Expression - Latest Sample Date and Second Latest Sample Date

HashemAbdo — Mon, 31 Oct 2022 18:08:42 GMT

I am trying to create a cleaned up table that shows the most recent available date for each ['siteID'] while still showing the associated COVID variant values [sys_estFreqReads_DeltaB16172,....).

In some cases some SiteIDs will have two samples for the same Date and I would like both samples to be shown in the final product output. I was trying to see if another If statement that would see if the date in the latest sample date but it seems like Arcade doesn't let me do that. Is there another way of doing it ?

Something like that:

// samples
var sample_list = OrderBy(samples, 'sampleDate DESC')

if(latest_sample.sampleDate == sample_list[1].sampleDate )
latest_sample = sample_list[1]

Updated code:

var portal = Portal('https://arcgis.com');

var fs = FeatureSetByPortalItem(
portal,
'XXXX',
0,
[
'sys_estFreqReads_DeltaB16172',
'sys_estFreqReads_OmicronBA1',
'sys_estFreqReads_OmicronBA2',
'sys_estFreqReads_OmicronBA4',
'sys_estFreqReads_OmicronBA5',
'sys_estFreqReads_OmicronBA275',
'sys_sewershedName',
'sys_phu',
'sampleDate',
'siteID',
'lowQuality'
],
false
);

var dowDict = {
'fields': [
{ 'name': 'sampleDate', 'type': 'esriFieldTypeString'},
{ 'name': 'siteID', 'type': 'esriFieldTypeString'},
{'name': 'sys_phu', 'type': 'esriFieldTypeString'},
{'name': 'sys_sewershedName', 'type': 'esriFieldTypeString'},
{ 'name': 'sys_estFreqReads_DeltaB16172', 'type': 'esriFieldTypeDouble'},
{ 'name': 'sys_estFreqReads_OmicronBA1', 'type': 'esriFieldTypeDouble'},
{ 'name': 'sys_estFreqReads_OmicronBA2', 'type': 'esriFieldTypeDouble'},
{ 'name': 'sys_estFreqReads_OmicronBA4', 'type': 'esriFieldTypeDouble'},
{ 'name': 'sys_estFreqReads_OmicronBA5', 'type': 'esriFieldTypeDouble'},
{ 'name': 'sys_estFreqReads_OmicronBA275', 'type': 'esriFieldTypeDouble'},
{ 'name': 'dow_num', 'type': 'esriFieldTypeInteger'},
{'name': 'dow', 'type': 'esriFieldTypeString'},
{'name': 'lowQuality', 'type': 'esriFieldTypeString'}

],
'geometryType': '',
'features': []
};
// get all distinct combinations of ['siteID','sys_phu', 'sys_sewershedName']
//var sites = Distinct(fs, ['siteID','sys_phu', 'sys_sewershedName'])
var sites = Distinct(fs, ['siteID'])

// Create array for holding features, feat object for populating array
var features = [];
var feat;

//var newdate2 = Text($datapoint["sampleDate"],'DD-MMM-YY');

// Populate feature array
for (var site in sites) {
var id = site.siteID
//var phu = site.sys_phu
//var sewershed = site.sys_sewershedName
// get all samples for this site
var samples = Filter(fs,'siteID = @ID')
// get the latest sample
var latest_sample = First(OrderBy(samples, 'sampleDate DESC'))

// samples
var sample_list = OrderBy(samples, 'sampleDate DESC')

// null check to avoid errors
if(latest_sample == null) { continue }
// create the new feature and push it into the output fs
feat = {
'attributes': {
'sampleDate': Text(latest_sample['sampleDate'],'D-MMM-YY'),
'siteID': (latest_sample['siteID']),
'sys_phu': Text(latest_sample['sys_phu']),
'sys_sewershedName': Text(latest_sample['sys_sewershedName']),
'sys_estFreqReads_DeltaB16172': (latest_sample['sys_estFreqReads_DeltaB16172']),
'sys_estFreqReads_OmicronBA1': (latest_sample['sys_estFreqReads_OmicronBA1']),
'sys_estFreqReads_OmicronBA2': (latest_sample['sys_estFreqReads_OmicronBA2']),
'sys_estFreqReads_OmicronBA4': (latest_sample['sys_estFreqReads_OmicronBA4']),
'sys_estFreqReads_OmicronBA5': (latest_sample['sys_estFreqReads_OmicronBA5']),
'sys_estFreqReads_OmicronBA275': (latest_sample['sys_estFreqReads_OmicronBA275']),
'dow_num': Weekday(latest_sample['sampleDate']),
'dow': Text(latest_sample['sampleDate'], 'dddd'),
'lowQuality': Text(latest_sample['lowQuality']),

}}
Push(dowDict.features, feat);
};

// Convert dictionary to feature set.
//var fs_dict = FeatureSet(Text(dowDict));
return FeatureSet(Text(dowDict));

@JohannesLindner ,

Re: Arcade Data Expression - Latest Sample Date and Second Latest Sample Date

JohannesLindner — Tue, 01 Nov 2022 11:36:46 GMT

Instead of getting the first row of the filtered featureset, you need a second for loop:

// Populate feature array for (var site in sites) { var id = site.siteID // get all samples for this site, ordered by date var samples = OrderBy(Filter(fs, 'siteID = @ID'), 'sampleDate DESC') var i = 0 var prevDate // loop through the samples for(var sample in samples) { // abort after second feature i += 1 if(i > 2) { break } // abort if second feature has different date if(i == 2 && Text(sample.sampleDate, 'Y-MM-DD') != Text(prevDate, 'Y-MM-DD')) { break } // store date prevDate = sample.sampleDate // create the new feature and push it into the output fs var feat = { 'attributes': { //... }} Push(dowDict.features, feat); } };

Re: Arcade Data Expression - Latest Sample Date and Second Latest Sample Date

HashemAbdo — Tue, 01 Nov 2022 19:27:47 GMT

This worked !!!! Thank you so much

topic Re: Arcade Data Expression - Latest Sample Date and Second Latest Sample Date in ArcGIS Dashboards Questions

Arcade Data Expression - Latest Sample Date and Second Latest Sample Date

Re: Arcade Data Expression - Latest Sample Date and Second Latest Sample Date

Re: Arcade Data Expression - Latest Sample Date and Second Latest Sample Date