If/Else Statement Field Calculator

GeoffreyWest · ‎02-03-2015

I would like to add a simple if statement to an existing expression, but am having difficulties getting it to execute. My previous expression in vb script concatenates a number of fields to make one long string in a new field. I would like to add multiple if else statements to be a part of this expression.

'Trim([NUMBERCYLA]) & ",SB,"& Trim([RESOLUTION_CODE]) & ",,,SC Truck,"& Trim([last_edited_user])& ",Driver,"& Month( [last_edited_date])&"/"& Day([last_edited_date])&"/"& Year ( [last_edited_date]) & ","'

I have a field SCCatDesc that contains the attributes MAT, MBE, MBI, MBW, SBE, and SBI.

If SCCatDesc = MAT then output = SB

If SCCatDesc = MBE then output = ME

If SCCatDesc = MBI then output = MB

If SCCatDesc = MBW then output = MW

If SCCatDesc = SBE then output = SB

If SCCatDesc = SBI then output = SBI

I have tried the following as start however it does not work.

If [SCCatDesc] = "MAT" Then
Output = "SB"
ElseIf [SCCatDesc] = "MBE" Then
Output = "ME"
Else
Output = "TEST"
End If

import arcpy

#Define Local Parameters
whereclause = "PlannedDate = CONVERT(DATE, GETDATE())"
SDEFeatureClass = "C:\Users\Administrator\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog\Connection to localhost_SCData_sa.sde\SCData.DBO.SO_SC"
LocalFGDB = "C:\PeterText.gdb"
Outable = "WLATable"
PeterTable = "C:\PeterText.gdb\WLATABLE"
expression = 'Trim([NUMBERCYLA]) & ",SB,"& Trim([RESOLUTION_CODE]) & ",,,SC Truck,"& Trim([last_edited_user])& ",Driver,"& Month( [last_edited_date])&"/"& Day([last_edited_date])&"/"& Year ( [last_edited_date]) & ","'

#Selection Query
selection = """CYLA_DISTRICT = 'WLA' AND PlannedDate = CONVERT(DATE, GETDATE())"""







#Deletes old FC
if arcpy.Exists(PeterTable):
  arcpy.Delete_management(PeterTable)


#Sends Feature Class to Table with Where Clause
arcpy.TableToTable_conversion(SDEFeatureClass, LocalFGDB, Outable, selection)

#Calculates Field with expression for Peter Text File
arcpy.CalculateField_management(PeterTable, "PtrText", expression)










#Search Cursor to extract Peter Text Field
myOutputFile = open("C:\PeterScripts\WLA\Peter.txt", 'w')
rows = arcpy.da.SearchCursor("C:\PeterText.gdb\WLATable", ["PtrText"])
for row in rows:
  myOutputFile.write(str(row[0]) + '\n')
del row, rows
myOutputFile.close()

BlakeTerhune · ‎02-03-2015

I think you've really got two field calcs here:

The IF statement to make the two letter codes
The long concatenation expression

I don't know how those two letter codes fit in to the concatenation expression. ArcGIS Help 10.2 - Calculate Field (Data Management) needs an expression type if you've got a code block to execute. The IF statement you have posted here is a code block in VB. For future compatibility and for the sake of this forum, I'll write it in Python.

codeblock = """def findTwoLetter(sccatdesc):
    output = None
    if sccatdesc == "MAT":
        output = "SB"
    elif sccatdesc == "MBE":
        output = "ME"
    elif sccatdesc == "ME":
        output = "TEST"
    return output"""

With that codeblock, you will need to include a call to the code block function (I temporarily called it findTwoLetter) with the field name as the argument. Something like

expression = "findTwoLetter(!SCCatDesc!)"

The final calculate field code would then be like

arcpy.CalculateField_management(PeterTable, "PtrText", expression, "PYTHON_9.3", codeblock)

Again, this does not include your concatenation because I don't know how it fits in.

XanderBakker · ‎02-03-2015

In addition to what Blake indicates, you could use a dictionary (key, value pairs) to hold the values you want to recode. In python this would be something like:

dct = {"MAT": "SB",  "MBE": "ME",
      "MBI": "MB", "MBW": "MW",
      "SBE": "SB", "SBI": "SBI"}

SCCatDesc = "MBI"
output = dct[SCCatDesc] if SCCatDesc in dct else "TEST"

...will result "MB"

With respect to the other expression you could do this in python:

from datetime import datetime
last_edited_date = datetime.now()
NUMBERCYLA = "NUMBERCYLA"
RESOLUTION_CODE = "RESOLUTION_CODE"
last_edited_user = "last_edited_user"
datetxt = last_edited_date.strftime("%m/%d/%Y")

result = "{0},SB,{1},,,SC Truck,{2},Driver,{3},".format(NUMBERCYLA, RESOLUTION_CODE, last_edited_user, datetxt)
print result

will yield:

>> NUMBERCYLA,SB,RESOLUTION_CODE,,,SC Truck,last_edited_user,Driver,02/03/2015,

I would probably use an arc.da.UpdateCursor to update the information...