topic Arcade Data Expression - GroupBy and Latest Sample Date in ArcGIS Dashboards Questions

Arcade Data Expression - GroupBy and Latest Sample Date

HashemAbdo — Wed, 26 Oct 2022 18:07:48 GMT

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 🙂

Re: Arcade Data Expression - GroupBy and Latest Sample Date

JohannesLindner — Thu, 27 Oct 2022 07:37:27 GMT

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

Re: Arcade Data Expression - GroupBy and Latest Sample Date

HashemAbdo — Thu, 27 Oct 2022 14:38:47 GMT

Hello Johannes, It worked !!!

You are amazing 🏆 !!

Re: Arcade Data Expression - GroupBy and Latest Sample Date

HashemAbdo — Thu, 27 Oct 2022 19:03:15 GMT

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