Field Calclator Issue (if Statement?)

612
3
12-14-2011 12:01 PM
Highlighted
New Contributor II
Hello All,

I am trying to fill data in a field through Model Builder which requires me to concatenate data from two fields and then separate the data with a hyphen.  The issue I have is that in many cases, the second field has no data and thus, there is no point to having a hyphen.  Is there a relatively simple if statement that could handle this issue?

Thank you for your help in advance!

Colter
Reply
0 Kudos
3 Replies
Highlighted
by
New Contributor
Dear Colter,

I'm stuck with almost the same problem. Did u find a solution?

Thx greetings
Philipp
Reply
0 Kudos
Highlighted
MVP Honored Contributor
It depends if the second field contains Null values or whitespace characters or a mixture of the two.  Assuming it is a mixture (and that the first field does not also need to be tested for blank or Null) try:

Dim Output
If IsNull([MyField2]) Then
  Output = [MyField1]
Elseif Trim([MyField2]) = "" Then
  Output = [MyField1]
Else
  Output = [MyField1] & "-" & [MyField2]
End If


If you plan on exporting to a Python script than this needs to be rewritten in Python to work.  However, this post suggests that Python cannot test for Null values in the field calculator like VB ...
Reply
0 Kudos
Highlighted
New Contributor
Python does allow for <null> check in field calculator.
Open field calculator, choose the Python Parser.
Check the "Show Codeblock" box
Enter the following in the "Pre-Logic Script Code:" box

def Concatenate(field1,field2):
  if field2 == "":
    return field1
  else:
    return field1 + '-' + field2


in the bottom box enter (change the stuff between the !'s to your actual field names):
Concatenate(str(!MyField1!), str(!MyField2!))


This works because the null values ( <null> ) are converted to empty strings before being sent to the custom "Concatenate" function build in the code block. More info can be found at this link

Additional options:
if both fields contain <null> values and/or you want to you want to retain said <null> values... this example code should help:

Parser: Python
PreLogic:
def myFunction(one,two):
  if one == "":   # check if first field is <null>
    if two == "": # check if both fields are <null> and returns <null>
      pass
    else:
      return two   # otherwise, just return the second field value
  else:                # check if the first field is populated...
    if two == "": # if second field is blank
      return one  # return just the first field
    else:             # if both fields are populated
      return one + "-" + two  # return a concatenation of the fields with a hyphen deliminator


Calculation box:
myFunction(str(!field1!), str(!field2!))
Reply
0 Kudos