# Compare fields of two tables and calculate field

Can you help me to compare fields in two tables? I have two tables, which contain ID and Measure fields. I need to take stroke by stroke values from field1(table1) and find all values in field1(table2) which are less than sought-for value. And then take largest of them and put it on stroke field2(table1). Example showed on picture below.

It's will be great, if it would be python script.

• I don't understand the logic you're using in your example excel table. Can you explain more clearly how you declare values for field 2 in your results table? This question may help you.
– Roy
Commented Sep 4, 2013 at 12:48
• As far as I can tell, he wants to find the largest value in table 2 that is less than the current value in table 1.
– Paul
Commented Sep 4, 2013 at 12:59
• Yes. And put in on field2 of 1st table ( stroke by stroke) Commented Sep 4, 2013 at 13:29
• I assume these tables are much larger than shown. What happens if the value of table1 is less than all values in table2?
– Roy
Commented Sep 4, 2013 at 13:37
• then field2 will equal field1 Commented Sep 4, 2013 at 13:41

I'm a GIS guy, not a professional programmer so I'm sure there's a more efficient way to write this. I tested the following code on the dummy data you provided. You'll have to make sure it works on an extended dataset (hence the extensive comments so you can modify it).

Pseudo Code:

1. Create a python list of all values in table 2 using a search cursor.
2. Loop through table 1 with an Update cursor testing against each value in your new list.
3. If current value in list is less than the value of table one, store it in a variable.
4. If current value in list is greater than the value in table one, use the variable from the last iteration to update field 2.

Script:

``````import arcpy

# Put the absolute path to your tables / feature classes
table1 = r"C:\SomePathHere"
table2 = r"C:\SomeOtherPathHere"

# Create empty Python list
table2list = []

# Arcpy Search cursor
rows = arcpy.SearchCursor(table2)

# Get a list of all values in table2 field1
for row in rows:
table2list.append(row.Field1)

# Delete cursor objs
del row, rows

# Sort the list in numeric order, get the highest value
table2list = sorted(table2list)
highestVal = table2list[len(table2list)-1]

# Create update cursor for table1
rows = arcpy.UpdateCursor(table1)

# Nested loop -- for each row in table1 we loop through our list of table2 values
for row in rows:
# Reset stored value var for each loop
storedValue = 0
# If Field1 is less than the smallest value in table2, Field2 is equal to Field1
if (row.Field1 < table2list[0] and row.Field < 11):
row.Field2 = 0
rows.updateRow(row)
elif (row.Field1 < table2list[0] and row.Field > 10):
row.Field2 = row.Field1
rows.updateRow(row)
# If Field1 is greater than the highest value, Field2 equals highest value
elif row.Field1 > highestVal:
row.Field2 = highestVal
rows.updateRow(row)

# When the value in the list is greater than Field1, use the last value to update Field2
for i in table2list:
if i < row.Field1:
storedValue = i
else: # This is where we're actually changing the value of Field2
row.Field2 = storedValue
rows.updateRow(row)

del row, rows
``````
• it's write that's some unknown problen has happenned Commented Sep 5, 2013 at 9:44
• i was trying to set 9 or 8 in field1 and there are 50 in field Commented Sep 5, 2013 at 9:50
• and when i'm writing 60 in field1 there is NULL value in Field2 Commented Sep 5, 2013 at 9:55
• Could you post your tables so I can take a look?
– Roy
Commented Sep 5, 2013 at 13:31
• <img src="s24.postimg.org/jcqj47ckl/1st_table.jpg"> <img src="s24.postimg.org/8r6ns7691/2d_Table.jpg"> Commented Sep 5, 2013 at 14:21