Using iterator to create new fields in a table based on values in a different table

Question

I have a table with a list of threatened species. Using model builder I want to add fields to a different layer - one field for every unique species value. The list will vary and the number of species will vary too so I can't create a predefined list of new fields.

Is this possible?

SteveH · Answer

Joins seem a bit clunky in model builder so I was going to do an iterative select by location for each species. I don't need species counts, this is just a presence /absence. So reading down Column ThrSpec_1 may read Devil, null, null, Devil, Devil etc. ThrSpec_2 may be null, Quoll, Quoll, null etc...

Part of the reason I wanted to do this iteratively was to create the columns on the fly as needed - select unique species value 1, create column, select parcels, fill values. Repeat for unique value 2. etc. I just don't know how to use an iterator to carry out a task on a different layer.

DanPatterson · Answer

Getting closer...

The first thing to ask, is did you do a spatial join of the polygon layer to the points to find out what which polygon the points belong to?

Second, there isn't going to be the same number of threatened species in each polygon I suspect, so you need to know ahead of time how many fields to create or a plan to deal with this.

Third, do you just want the species name or the species name and number to be recorded if there was more than one recording? (thinking ahead).

Final table layout????

PolyID, ThrSpec_1, N_1, ThrSpec_2,  N_2, ThrSpec_3, N_3, ....  ThrSpec_N, N_n

1           parrot,  1000,  budgie,  5, crow, 300

2           parrot,         3,  budgie,  0,  crow,     0

3            parrot,         0,  budgie, 10,  crow,    0

OR!!!

PolyID, Parrot, Budgie,  Crow, Turkey_Vulture,

1           1000,            5,    , 300,                       10

2                  3,            0,          0,                   1000

3                   0,          10,          0,                          0

OR !!!!

something completely different?

SteveH · Answer

Thanks Dan, this is a small part of a much larger model I'm building in Model builder. I'm kind of new to iterators so I'm not sure how to set this up.

All of the species are in a single field in a point file and I would prefer to create Text fields named ThrSpec_1, ThrSpec_2 etc in a Polygon file of land parcels and populate the fields of selected records with the relevant species name.

Does that make sense?

Thanks, Steve