Arcade Data Expression - GroupBy and Latest Sample Date

[HashemAbdo]

Hello,

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

My issue is that i am trying to show these fields that are associated with the GroupBy tool without running any calculations to the variants. Is GroupBy the right tool here ? Would some be able to help me ?

{name:'sys_estFreqReads_DeltaB16172', expression:'sys_estFreqReads_DeltaB16172', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA1', expression:'sys_estFreqReads_OmicronBA1', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA2', expression:'sys_estFreqReads_OmicronBA2', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA4', expression:'sys_estFreqReads_OmicronBA4', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA5', expression:'sys_estFreqReads_OmicronBA5', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA275', expression:'sys_estFreqReads_OmicronBA275', statistic:'MAX'}
]

Code below

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

var fs = FeatureSetByPortalItem(
portal,
'XXX',
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
);

//create table
var fs_gp = GroupBy(
fs,
['siteID','sys_phu', 'sys_sewershedName'],
[{name:'sampleDate', expression:'sampleDate', statistic:'MAX'},
{name:'sys_estFreqReads_DeltaB16172', expression:'sys_estFreqReads_DeltaB16172', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA1', expression:'sys_estFreqReads_OmicronBA1', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA2', expression:'sys_estFreqReads_OmicronBA2', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA4', expression:'sys_estFreqReads_OmicronBA4', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA5', expression:'sys_estFreqReads_OmicronBA5', statistic:'MAX'},
{name:'sys_estFreqReads_OmicronBA275', expression:'sys_estFreqReads_OmicronBA275', statistic:'MAX'}
]
);

// 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 feature in fs_gp) {
// Console(feature['sampleDate']);
feat = {
'attributes': {
'sampleDate': Text(feature['sampleDate'],'D-MMM-YY'),
'siteID': (feature['siteID']),
'sys_phu': Text(feature['sys_phu']),
'sys_sewershedName': Text(feature['sys_sewershedName']),
'sys_estFreqReads_DeltaB16172': (feature['sys_estFreqReads_DeltaB16172']),
'sys_estFreqReads_OmicronBA1': (feature['sys_estFreqReads_OmicronBA1']),
'sys_estFreqReads_OmicronBA2': (feature['sys_estFreqReads_OmicronBA2']),
'sys_estFreqReads_OmicronBA4': (feature['sys_estFreqReads_OmicronBA4']),
'sys_estFreqReads_OmicronBA5': (feature['sys_estFreqReads_OmicronBA5']),
'sys_estFreqReads_OmicronBA275': (feature['sys_estFreqReads_OmicronBA275']),
'dow_num': Weekday(feature['sampleDate']),
'dow': Text(feature['sampleDate'], 'dddd')

}}
Push(features, feat);
};
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'}
],
'geometryType': '',
'features': features
};
// Convert dictionary to feature set.
//var fs_dict = FeatureSet(Text(dowDict));
return FeatureSet(Text(dowDict));

From the previous postings i feel like  @JohannesLindner is an ALL STAR with Arcade Challenges 🙂

[JohannesLindner · Accepted solution]

You have to do it in two steps:

• Use GroupBy to get the MAX date for each site
• Use a loop to fill your output featureset, filtering the input data

I hate filtering with date values with a passion, because any number of things can go wrong, and the syntax depends on your database. I would use Distinct() instead:

var p = Portal('https://arcgis.com');  // don't assign to names of functions!
var fs = FeatureSetByPortalItem(
    p,
    'XXX',
    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
);

// create output fs
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'}
        ],
    'geometryType': '',
    'features': []
};

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

// 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 AND sys_phu = @phu AND sys_sewershedName = @sewershed')
    // get the latest sample
    var latest_sample = First(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': Number(latest_sample['sampleDate']),
            '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')
    }}
    Push(dowDict.features, feat);
};

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

[HashemAbdo]

Hello Johannes, It worked !!!

You are amazing 🏆 !!

[HashemAbdo]

Hello  @JohannesLindner ,

I have one more question,

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