I created an arcade expression that takes a feature service and calculates some stats and outputs the results into a FeatureSet.
The problem that I run into is some of my Dict is not writing to my FeatureSet. When using the Console I can see that everything has written properly to my dictionary but seems to be missing from the FeatureSet.
I highlighted the two rows that are missing.
var Trap_Locations_Tab = FeatureSetByPortalItem(Portal('https://xxxx.arcgis.com/'), '99999999999999999999', 0, ['*'])
var Year_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year IS NOT NULL'), 'Year')
var TrapDict = {'fields': [{'name':'Pests', 'type':'esriFieldTypeString'},
{'name':'Year', 'type':'esriFieldTypeInteger'},
{'name':'Total_Num_Traps', 'type':'esriFieldTypeInteger'},
{'name':'Total_Trap_Checks', 'type':'esriFieldTypeInteger'},
{'name':'Traps_Checked_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Total_Moth_Count_Sum', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Prior_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Counts_Sum_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Average_Moth_Count_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Change_Trap_Counts', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Change', 'type':'esriFieldTypeInteger'}
], 'geometryType': '', 'features': []};
var dt_7 = DateAdd(Date(Max(Trap_Locations_Tab, 'Trap_Check_Date')), -7, 'days')
for (var Y in Year_Tab) {
var Pest_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year = ' + Y.Year), 'Pest')
for (var P in Pest_Tab) {
var PSQL = "Pest = '" + P.Pest + "' And " + 'Year = ' + Y.Year
var PrevSQL = "Pest = '" + P.Pest + "' And " + "Year = " + Y.Year + " And Trap_Check_Date > '" + dt_7 + "'"
var PriorSQL = "Pest = '" + P.Pest + "' And " + "Year = " + Y.Year + " And Trap_Check_Date < '" + dt_7 + "'"
var Total_Trap_Cnt = Count(Distinct(Filter(Trap_Locations_Tab, PSQL), 'ParentGlobalID'))
var Total_Trap_Checks = Count(Filter(Trap_Locations_Tab, PSQL))
var Trap_Checks_Last_Week = Count(Filter(Trap_Locations_Tab, PrevSQL))
var Moth_Count_Sum = Sum(Filter(Trap_Locations_Tab, PSQL), 'Counts')
var Moth_Count_Sum_Prior_Last_Week = Sum(Filter(Trap_Locations_Tab, PriorSQL), 'Counts')
var Moth_Count_Sum_Last_Week = Sum(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
var Moth_Count_Mean_Last_Week = Mean(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
Push(TrapDict.features,{
'attributes': {
'Pests': Decode(P.Pest, 'WBC', 'Western Bean Cutworm', 'ECB_E', 'European Corn Borer - E (New York Strain)', 'ECB_Z', 'European Corn Borer - Z (Iowa Strain)', 'ECB_H', 'European Corn Borer Hybrid', 'CEW', 'Corn Earworm', 'BCW', 'Black Cutworm', 'TAW', 'True Armyworm', 'FAW', 'Fall Armyworm', 'Other'),//P.Pest,
'Year': Y.Year,
'Total_Num_Traps': Total_Trap_Cnt,
'Total_Trap_Checks': Total_Trap_Checks,
'Traps_Checked_Last_Week': Trap_Checks_Last_Week,
'Total_Moth_Count_Sum': Moth_Count_Sum,
'Moth_Count_Sum_Prior_Last_Week': Moth_Count_Sum_Prior_Last_Week,
'Moth_Counts_Sum_Last_Week': Moth_Count_Sum_Last_Week,
'Average_Moth_Count_Last_Week': Moth_Count_Mean_Last_Week}
})
}}
Console(TrapDict)
return FeatureSet(Text(TrapDict));
And the value (CEW) for 2024 that is included falls between the two that are missing... alphabetically (BCW, CEW, TAW).
Any help would be greatly appreciated.
Cheers!
Solved! Go to Solution.
Well... I think I answered my own question. The issue was around the Date SQL. I was doing my Date calculations from my service as opposed to using a new Date variable Today().
var dt_7 = DateAdd(Date(Max(Trap_Locations_Tab, 'Trap_Check_Date')), -7, 'days')
As opposed to...
var dt = Today()
var dt_7 = DateAdd(dt, -7, 'days')
I need this change detection for my dashboard.
Here's is my finished code just in case it may help someone else 😁
var Trap_Locations_Tab = FeatureSetByPortalItem(Portal('XXXXXXXx.arcgis.com/'), '9999999999999999999999999999', 0, ['*'])
var Year_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year IS NOT NULL'), 'Year')
var TrapDict = {'fields': [{'name':'Pests', 'type':'esriFieldTypeString'},
{'name':'Year', 'type':'esriFieldTypeInteger'},
{'name':'Total_Num_Traps', 'type':'esriFieldTypeInteger'},
{'name':'Total_Trap_Checks', 'type':'esriFieldTypeInteger'},
{'name':'Traps_Checked_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Total_Moth_Count_Sum', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Prior_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Counts_Sum_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Average_Moth_Count_Last_Week', 'type':'esriFieldTypeDouble'},
{'name':'Change_Trap_Counts', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Change', 'type':'esriFieldTypeInteger'}
], 'geometryType': '', 'features': []};
var dt = Today()
var dt_7 = DateAdd(dt, -7, 'days')
for (var Y in Year_Tab) {
var Pest_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year = ' + Y.Year), 'Pest')
for (var P in Pest_Tab) {
var PSQL = "Pest = '" + P.Pest + "' And " + 'Year = ' + Y.Year
var PrevSQL = "Pest = '" + P.Pest + "' And " + "Year = " + Y.Year + " And Trap_Check_Date > '" + dt_7 + "'"
var PriorSQL = "Pest = '" + P.Pest + "' And " + "Year = " + Y.Year + " And Trap_Check_Date < '" + dt_7 + "'"
var Total_Trap_Cnt = Count(Distinct(Filter(Trap_Locations_Tab, PSQL), 'ParentGlobalID'))
var Total_Trap_Checks = Count(Filter(Trap_Locations_Tab, PSQL))
var Trap_Checks_Last_Week = Count(Filter(Trap_Locations_Tab, PrevSQL))
var Moth_Count_Sum = Sum(Filter(Trap_Locations_Tab, PSQL), 'Counts')
var Moth_Count_Sum_Prior_Last_Week = Sum(Filter(Trap_Locations_Tab, PriorSQL), 'Counts')
var Moth_Count_Sum_Last_Week = Sum(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
var Moth_Count_Mean_Last_Week = Mean(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
Push(TrapDict.features,{
'attributes': {
'Pests': Decode(P.Pest, 'WBC', 'Western Bean Cutworm', 'ECB_E', 'European Corn Borer - E (New York Strain)', 'ECB_Z', 'European Corn Borer - Z (Iowa Strain)', 'ECB_H', 'European Corn Borer Hybrid', 'CEW', 'Corn Earworm', 'BCW', 'Black Cutworm', 'TAW', 'True Armyworm', 'FAW', 'Fall Armyworm', 'Other'),//P.Pest,
'Year': Y.Year,
'Total_Num_Traps': Total_Trap_Cnt,
'Total_Trap_Checks': Total_Trap_Checks,
'Traps_Checked_Last_Week': Trap_Checks_Last_Week,
'Total_Moth_Count_Sum': Moth_Count_Sum,
'Moth_Count_Sum_Prior_Last_Week': Moth_Count_Sum_Prior_Last_Week,
'Moth_Counts_Sum_Last_Week': Moth_Count_Sum_Last_Week,
'Average_Moth_Count_Last_Week': Moth_Count_Mean_Last_Week}
})
}}
return FeatureSet(Text(TrapDict));
~Dan
Well... I think I answered my own question. The issue was around the Date SQL. I was doing my Date calculations from my service as opposed to using a new Date variable Today().
var dt_7 = DateAdd(Date(Max(Trap_Locations_Tab, 'Trap_Check_Date')), -7, 'days')
As opposed to...
var dt = Today()
var dt_7 = DateAdd(dt, -7, 'days')
I need this change detection for my dashboard.
Here's is my finished code just in case it may help someone else 😁
var Trap_Locations_Tab = FeatureSetByPortalItem(Portal('XXXXXXXx.arcgis.com/'), '9999999999999999999999999999', 0, ['*'])
var Year_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year IS NOT NULL'), 'Year')
var TrapDict = {'fields': [{'name':'Pests', 'type':'esriFieldTypeString'},
{'name':'Year', 'type':'esriFieldTypeInteger'},
{'name':'Total_Num_Traps', 'type':'esriFieldTypeInteger'},
{'name':'Total_Trap_Checks', 'type':'esriFieldTypeInteger'},
{'name':'Traps_Checked_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Total_Moth_Count_Sum', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Prior_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Counts_Sum_Last_Week', 'type':'esriFieldTypeInteger'},
{'name':'Average_Moth_Count_Last_Week', 'type':'esriFieldTypeDouble'},
{'name':'Change_Trap_Counts', 'type':'esriFieldTypeInteger'},
{'name':'Moth_Count_Sum_Change', 'type':'esriFieldTypeInteger'}
], 'geometryType': '', 'features': []};
var dt = Today()
var dt_7 = DateAdd(dt, -7, 'days')
for (var Y in Year_Tab) {
var Pest_Tab = Distinct(Filter(Trap_Locations_Tab, 'Year = ' + Y.Year), 'Pest')
for (var P in Pest_Tab) {
var PSQL = "Pest = '" + P.Pest + "' And " + 'Year = ' + Y.Year
var PrevSQL = "Pest = '" + P.Pest + "' And " + "Year = " + Y.Year + " And Trap_Check_Date > '" + dt_7 + "'"
var PriorSQL = "Pest = '" + P.Pest + "' And " + "Year = " + Y.Year + " And Trap_Check_Date < '" + dt_7 + "'"
var Total_Trap_Cnt = Count(Distinct(Filter(Trap_Locations_Tab, PSQL), 'ParentGlobalID'))
var Total_Trap_Checks = Count(Filter(Trap_Locations_Tab, PSQL))
var Trap_Checks_Last_Week = Count(Filter(Trap_Locations_Tab, PrevSQL))
var Moth_Count_Sum = Sum(Filter(Trap_Locations_Tab, PSQL), 'Counts')
var Moth_Count_Sum_Prior_Last_Week = Sum(Filter(Trap_Locations_Tab, PriorSQL), 'Counts')
var Moth_Count_Sum_Last_Week = Sum(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
var Moth_Count_Mean_Last_Week = Mean(Filter(Trap_Locations_Tab, PrevSQL), 'Counts')
Push(TrapDict.features,{
'attributes': {
'Pests': Decode(P.Pest, 'WBC', 'Western Bean Cutworm', 'ECB_E', 'European Corn Borer - E (New York Strain)', 'ECB_Z', 'European Corn Borer - Z (Iowa Strain)', 'ECB_H', 'European Corn Borer Hybrid', 'CEW', 'Corn Earworm', 'BCW', 'Black Cutworm', 'TAW', 'True Armyworm', 'FAW', 'Fall Armyworm', 'Other'),//P.Pest,
'Year': Y.Year,
'Total_Num_Traps': Total_Trap_Cnt,
'Total_Trap_Checks': Total_Trap_Checks,
'Traps_Checked_Last_Week': Trap_Checks_Last_Week,
'Total_Moth_Count_Sum': Moth_Count_Sum,
'Moth_Count_Sum_Prior_Last_Week': Moth_Count_Sum_Prior_Last_Week,
'Moth_Counts_Sum_Last_Week': Moth_Count_Sum_Last_Week,
'Average_Moth_Count_Last_Week': Moth_Count_Mean_Last_Week}
})
}}
return FeatureSet(Text(TrapDict));
~Dan