Field calculator with Python or SQL

Question

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.

GroupByOptionArcGISScShot.jpg

Screenshot 2024-05-14 213608.png

jcarlson · Answer

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 var max_le = Max(id_feats, 'AdjRiverLe') // Assign 'Yes' to max value, 'No' to others if($feature.AdjRiverLe == max_le){ return 'Yes' } else { return 'No' }

jcarlson · Answer

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.

JoshuaBixby · Answer

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
flds = ["ID", "AdjRiverLe", "River"]
sql = "ORDER BY ID, AdjRiverLe DESC"

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

for rid, adj_river, _ in cur:
        if rid == prev_rid:
            river = "NO"
        else:
            river = "YES"
        cur.updateRow([rid, adj_river, river])
        prev_rid = rid