Failed to query Statistics - Attribute rule

918
2
Jump to solution
06-01-2021 11:47 AM
JimWilliams
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

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"

Have a great day!
Johannes

View solution in original post

2 Replies
JohannesLindner
MVP Frequent Contributor

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"

Have a great day!
Johannes
JimWilliams
New Contributor III

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

0 Kudos