add and populate columns using model builder

186
3
07-22-2021 07:37 AM
SarahHartholt
Occasional Contributor II

I am creating a model in ArcPro for a suitability analysis. I have 3 factors that will influence my final score. Right now I run a series of calculations to determine a score for each factor. What I would like to do is add up the score from each factor to determine a final score (Score A + Score B + Score C).

I would like to use a join/merge or other tool to create a table that contains 1 polygon feature for each area of interest (OBJECTID) populated with a score fields from 3 separate tables. The merge tool appends all of the tables together like this:

OBJECTIDA_SCOREB_SCOREC_SCOREFinal_SCORE
10000
102.502.5
10018.1318.13
25005
20505
20021.821.8

 

but what I would like to see is 1 row per OBJECTID which should look like this:

OBJECTIDA_SCOREB_SCOREC_SCOREFinal_SCORE
102.518.1320.63
25521.831.8

 

Which tools should I be using to accomplish this? I am currently only including 3 factors but will have 9 in total so I'm hoping there are some batch tools available to make the model less complicated.

0 Kudos
3 Replies
markcrow1
New Contributor III

As with anything like this, there are lots of ways to approach aggregating the data the way you describe.  The easiest way I can think of is using the "Summarize" tool found in the Attribute Table.  In your case, you would open the attribute table, right-click on the "ObjectID" field and then click "Summarize".  Set all of your "*_SCORE" fields up in the "Statistic Fields" to sum, and the "Case Field" should already se set to the Object ID.  This will result in a new table that will have one row for every unique ID (Case Field entry) and fields for every Statistics Field you defined with sums of all of the records used to combine it.  You'll also get a count for each row indicating how many were aggregated.  Hope this helps

JayantaPoddar
MVP Esteemed Contributor

Do you have the data as a feature class?

I hope you have Added the "Final_Score" Field, and Calculated the field with the expression that you mentioned.

Then, you could use Dissolve tool, as mentioned below

JayantaPoddar_0-1626969729837.png

 



Think Location
0 Kudos
SarahHartholt
Occasional Contributor II

It sounds like the dissolve tool would work but my data is stored in a series of tables, not feature classes, so I'm not sure if it will work. 

My workaround for now has been to export one of the tables into a new table (Final_SCORE), join the score field from each of the other tables (2 joins) and then run a series of calculations to get my final score. I would like to slim it down if possible though since I will be adding more factors in the near future.

SarahHartholt_0-1626975951945.png

 

0 Kudos