eknaap

How to iterate a single selection over multiple fields (ModelBuilder or Python)

Discussion created by eknaap on Aug 13, 2013
Latest reply on Sep 6, 2013 by mstead
I have a file that contains point data for several thousand residential locations. The file contains locations spread across a 15 year span, and the attribute table contains binary fields indicating whether a household was occupied in a particular location in a given year (2000, 2001, 2002, etc). There are also a few fields that include neighborhood characteristics for each location.

A simplified version of the table looks something like this:
("Loc*" indicates whether a household was present at that location for the given year and "data" represents a field for which I need summary statistics)
[TABLE="class: grid"]ID
Loc00Loc01Loc02Loc03Data23410003472351110124352360111354
[/TABLE]

I am trying to create a time series of the average neighborhood characteristics of these residences by year. Basically, I am trying to iterate a selection over each Location field, selecting only the points occupied during a single year (Loc*=1) then compute the mean value for a few data columns and save those values in a table.

I have not been able to automate this with ModelBuilder because the Select by Attributes tool does not perform sequential iteration like I need. Instead, it uses grouping variables to create every unique combination of location (e.g. Loc00=1,Loc01=0, etc). Trying to use a list of values for the Select by Attribute tool (as opposed to an Iterator) does not work either because the model simply performs each selection in sequence before calculating any statistics (I've tried setting every precondition possible)

Does anyone know if it is possible to create a single SQL selection that will iterate over a specified set of fields?

Outcomes