# Field calculator with Python or SQL

923
5
06-26-2021 08:25 PM
by
New Contributor

Hi mates,

I would like to calculate/fill the fields- 'River' with the values either 'Yes' or 'No' depending on the maximum value in the field named 'AdjRiverLe' and the field named 'ID'. See the image here.

The rule will be:

Yes= The maximum value in the field 'AdjRiverLe' group by the field 'ID'

No= the rest of the values in the field 'AdjRiverLe'

For example, the ID 1 has three individual rows with three different values in the field- 'AdjRiverLe '.  I would like to select the row which belongs to the maximum values in the field - 'AdjRiverLe' and fill the field 'River' with 'Yes'.

I reckon the code block option would work.

5 Replies
Occasional Contributor II

Yes, the code block would work for your situation.

by
New Contributor

MVP Esteemed Contributor

Questions along these lines get asked fairly regularly on the Esri Community, probably every 4-6 months.  Although it is possible to answer your question using Field Calculator, it is quite a clunky approach in my mind.  This kind of question is best suited for ArcPy cursors:

``````lyr = # name of layer or path to data set
sql = "ORDER BY ID, AdjRiverLe DESC"

with arcpy.da.UpdateCursor(lyr, flds, sql_clause=(None, sql)) as cur:
river = "YES"
prev_rid = rid

for rid, adj_river, _ in cur:
if rid == prev_rid:
river = "NO"
else:
river = "YES"
prev_rid = rid``````

by
MVP Esteemed Contributor

Since you mention SQL, is the layer in question in an Enterprise GeoDatabase, or is it a feature service? There are a couple of ways you might approach this, but it may depend on where the features are coming from.

- Josh Carlson
Kendall County GIS
by
MVP Esteemed Contributor

Also, though you didn't mention Arcade, It's worth pointing out that this can be done simply enough in Arcade.

``````// Get features w/ matching ID
var id = \$feature.ID
var id_feats = Filter(FeatureSetByName(\$datastore, 'polygon-layer-name'), 'ID = @ID')

// Get maximum value of AdjRiverLe field