topic Re: Field calculator with Python or SQL in Geoprocessing Questions

Field calculator with Python or SQL

MDBaky — Sun, 27 Jun 2021 03:25:44 GMT

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.

Re: Field calculator with Python or SQL

Waffle_House — Sun, 27 Jun 2021 09:56:58 GMT

Yes, the code block would work for your situation.

Re: Field calculator with Python or SQL

MDBaky — Sun, 27 Jun 2021 11:39:30 GMT

Thanks Waffle for you reply. But would you please help me to figure the code block writing out?

Re: Field calculator with Python or SQL

JoshuaBixby — Sun, 27 Jun 2021 16:25:44 GMT

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

Re: Field calculator with Python or SQL

jcarlson — Mon, 28 Jun 2021 13:44:03 GMT

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.

Re: Field calculator with Python or SQL

jcarlson — Mon, 28 Jun 2021 13:52:24 GMT

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' }