2

Anyone have experience calculating a field based on a many to one scenario?

I have a feature class in a geodatabase with a field of unique names.


Feature Class Table (let's call this "MAINTABLE") looks like this:

OID   ;  Name ; B
1    ;   Breakfast ; NULL
2    ;   Lunch ; NULL
3    ;   Dinner ; NULL
4    ;   Dessert ; NULL

I also have a stand alone table in a different location that has several entries for each unique name in the feature class.


Stand alone table (Let's call this "SIDETABLE") looks like this:

UID ; Name ; A
1 ; Breakfast ; 0
2 ; Breakfast ; -1
3 ; Lunch ; -1
4 ; Dinner ; 0
5 ; Dessert ; NULL
6 ; Breakfast ; -1
7 ; Breakfast ; NULL
8 ; Breakfast ; -1
9 ; Breakfast ; -1
10 ; Breakfast ; NULL

I need help to search all values ("0" or "-1" or NULL) in SIDETABLE field "A", and if any entry in SIDETABLE field "A" has a "0", calculate field "B" in MAINTABLE as value "0".

BUT if a unique name entry in SIDETABLE field "A" contains "0" and "-1", calculate field "B" in MAINTABLE as value "0".

If a unique name from MAINTABLE does not in field "A" of SIDETABLE have value "0" but has value "-1", calculate field "B" in MAINTABLE as "-1".

If a unique name from MAINTABLE does not in field "A" of SIDETABLE have value "0" but has NULL value, calculate/keep field "B" in MAINTABLE as NULL.


The resulting feature class table ("MAINTABLE") should look like this:

OID   ;  Name ; B
1    ;   Breakfast ; 0
2    ;   Lunch ; -1
3    ;   Dinner ; 0
4    ;    Dessert ; NULL

***I would assume python is the best option. I am new scripting/ using python or whichever is best practice. I am assuming I would need some sort of conditional statement using Calculate Field on field "B" in the feature class. And I am also assuming I need to relate the feature class to table based on NAME.

***I am using ArcGIS 10.2 for Desktop Advanced


The following is the sort of logic I'm looking for

if A.SIDETABLE = 0 
return into B.MAINTABLE the value "0"

*[if there are 10 "breakfast" entries in SIDETABLE, 
  look at all values in field "A" and if one of the values is 0, 
  then return "0" into B.MAINTABLE]

else if A.SIDETABLE = -1 
return into B.MAINTABLE the value "-1"
else if A.SIDETABLE is NULL 
return into B.MAINTABLE NULL
  • What have you tried so far? Your logic is about right, but I don't really understand what the difference is when the end result for breakfast is the same value (being 0). Could you further clarify that point? Are you using unique IDS/ Joining your tables together? – GISKid Jun 25 '14 at 16:01
  • I think the logic this person is looking for is: if field A is ever False (0) for any record, then Field B should be False (0) for all records. What isn't clear is: if field A is ever NULL for any record, is it then NULL for all records. – ike Jun 25 '14 at 16:30
  • Query that solves this problem, depending on the case of nulls CASE_A solves for ignoring NULL, CASE_B preserves nulls: SELECT Name, A, MAX(A) OVER (PARTITION BY Name) AS CASE_A, CASE WHEN MAX(CASE WHEN A IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY Name)= 0 THEN MAX(A) OVER (PARTITION BY Name) END AS CASE_B – ike Jun 25 '14 at 16:34
  • @GISKid I need to relate the feature class table to the standalone table. There are multiple entries in the stand alone table for "breakfast" and only one entry for "breakfast" in the feature class. Hence I need to run a query for all entries in the stand alone table for all name values "breakfast", then check on attribute field "A" if there is a "0" in any of the entries, then return "0" into the feature class table attribute field "B". This query needs to be repeated for each unique name in the feature class. If there isn't a "0" like in "lunch" above which only has (-1) then return (-1). – baconANDeggs Jun 25 '14 at 17:21
  • I think part of this is the particular angle of approach you're taking. You're looking at all SIDETABLE records, when you only care about unique Name/A combinations, and really just the max unique combination. So you don't need to iterate through every SIDETABLE record, you need to collapse that table down or better yet just snag the MAX A per Name. – Chris W Jun 26 '14 at 0:47
1

Run Summary Statistics on SIDETABLE using Name as case field and A as statistics field with MAX as the statistic. You can then update MAINTABLE with the resulting summary table - regular join on Name and field calculate, join field, field calculate directly if both tables in the same geodatabase, etc.

For that matter, if the tables are in the same geodatabase and MAINTABLE doesn't have a lot of records, you could skip Summary Statistics and do this all in the field calculator. Psuedo-code for pre-logic block (and I might be blending Python and VB parsing here):

create a variable
CASE or IF statement on Name variants
for each Name variant, variable = SELECT MAX(A) FROM SIDETABLE WHERE Name = variant

Then your field calculation box would just be variable. Note per ike's statement, I'm not paying any attention to nulls here which may require further refinement or their own case/if. And in order to do a nested query like that, both tables have to be in the same geodatabase.

  • Wow. This works. And is super simple. Now it would be nice to figure out how to automate updates to B.MAINTABLE this when A.SIDETABLE gets updated with new "0" and "-1" values. Is there a way this can be scripted in my MXD if the feature class is joined to the table? – baconANDeggs Jun 25 '14 at 23:39
  • @baconANDeggs You can turn it into a model or script and run it manually whenever you get updates, but I've no clue how to do it 'automatically on join'. I think that would get into actions and triggers which I've yet to learn. I imagine it would go more akin to the comment @ike made - essentially you'd need a trigger on B.MAINTABLE that automatically grabbed the MAX by Name from A.SIDETABLE whenever SIDETABLE was changed. Or maybe the trigger is on SIDETABLE such that change = update MAINTABLE. – Chris W Jun 25 '14 at 23:56
  • Thank you for the information @Chris W. I will be doing some research on actions and triggers which I have had no experience with. I currently update non networked machines and data transfer at this point is a manual task; but automating this change to export the one feature class I use would be nice help. – baconANDeggs Jul 1 '14 at 14:49
0

Before calculating, I would join the tables.

  1. define a definition query on table A: FieldName in ("0", "-1", NULL)
  2. define a definiton query on table B to include the records that need to be updated
  3. Join table A to table B
  4. Now, use the field calculator and define the logic in the Python script

    I assume the relationship between table B to A is many to one. When joining the tables, use Keep all records as a Join Option.

  • I cannot use "keep all records" and a join because this creates multiple "breakfast" features in the feature class. I need to have only one unique "breakfast" feature in the feature class that searches for a specific value in the table that contains multiple "breakfast" entries. If there are 10 "breakfast" entries in the table (nine "-1" and one "0" in table attribute field "A"), I need "breakfast" in the feature class to have the value "0" in attribute field "B". – baconANDeggs Jun 25 '14 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.