topic Failed to query Statistics - Attribute rule in ArcGIS Pro Questions

Failed to query Statistics - Attribute rule

JimWilliams — Tue, 01 Jun 2021 18:47:59 GMT

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

Re: Failed to query Statistics - Attribute rule

JohannesLindner — Wed, 02 Jun 2021 06:34:21 GMT

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"

Re: Failed to query Statistics - Attribute rule

JimWilliams — Wed, 02 Jun 2021 17:34:36 GMT

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