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

HashemAbdo

 

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,
'xyz',
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 ,

JohannesLindner

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 = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/354972">@ID</a>'), '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);
    }
};