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.
Yes, the code block would work for your situation.
Thanks Waffle for you reply. But would you please help me to figure the code block writing out?
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
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.
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'
}