Hello,
I am stuck here. I need to filter a feature set by a date a value. I set up this up but on lie 7 for the "cnt" variable I am getting "Failed to Query statistics", but If I pull out the "AND MMS_Date_Turning >= @current_year" section from my filter it works fine.
This query fails:
var quad = $feature.Quadrant;
if ($feature.Quadrant != null) {
var current_year = Date(Year(Now()),0,1)
var System_valve_set = filter(featuresetbyname($datastore, 'sValve', ['OBJECTID', 'WATERVALVETURNING_STATUS', 'GRID_NUM', 'MMS_Date_Turning'], false), "GRID_NUM = @quad AND MMS_Date_Turning >= @current_year")
var cnt = count(System_valve_set);
if (cnt > 0 ) {
var query = "WATERVALVETURNING_STATUS = 'Closed'"
var System_closed_valve_set = filter(System_valve_set, query)
var closed_cnt = count(System_closed_valve_set)
if (closed_cnt > 0){
return count(System_valve_set) - count(System_closed_valve_set)
}
else {return count(System_valve_set)}
}
else { return null }
}
else { return null }
This query works:
var quad = $feature.Quadrant;
if ($feature.Quadrant != null) {
var current_year = Date(Year(Now()),0,1)
var System_valve_set = filter(featuresetbyname($datastore, 'sValve', ['OBJECTID', 'WATERVALVETURNING_STATUS', 'GRID_NUM', 'MMS_Date_Turning'], false), "GRID_NUM = @quad")
var cnt = count(System_valve_set);
if (cnt > 0 ) {
var query = "WATERVALVETURNING_STATUS = 'Closed'"
var System_closed_valve_set = filter(System_valve_set, query)
var closed_cnt = count(System_closed_valve_set)
if (closed_cnt > 0){
return count(System_valve_set) - count(System_closed_valve_set)
}
else {return count(System_valve_set)}
}
else { return null }
}
else { return null }
Regards,
Jim
Solved! Go to Solution.
Things to try:
// expand the date to include time
var current_year = Date(Year(Now()), 0, 1, 0, 0, 0)
// use text instead
var current_year = Text(Date(Year(Now()), 0, 1), "Y-MM-DD")
var sql = "GRID_NUM = @quad AND MMS_Date_Turning >= date @current_year"
// use the YEAR function in SQL:
var current_year = Year(Now())
var sql = "GRID_NUM = @quad AND YEAR(MMS_Date_Turning) >= @current_year"
Things to try:
// expand the date to include time
var current_year = Date(Year(Now()), 0, 1, 0, 0, 0)
// use text instead
var current_year = Text(Date(Year(Now()), 0, 1), "Y-MM-DD")
var sql = "GRID_NUM = @quad AND MMS_Date_Turning >= date @current_year"
// use the YEAR function in SQL:
var current_year = Year(Now())
var sql = "GRID_NUM = @quad AND YEAR(MMS_Date_Turning) >= @current_year"
Hey Johannes,
Thanks for the reply super helpful. I am surprised the only option that worked was making the date text. Nothing else seemed to work. Might follow up with ESRI to see if this is a known bug. Below is what worked when filtering with dates.
var current_year = Text(Date(Year(Now()), 0, 1), "Y-MM-DD")
var sql = "GRID_NUM = @quad AND MMS_Date_Turning >= date @current_year"
Thanks,
Jim