Join Field Not Working

348
8
12-23-2020 01:28 PM
LeviCecil
Occasional Contributor III

I've been having this problem lately, where the join field tool won't work in Pro 2.6 or 2.7, or Python 3.7.9. I'll try to join on two fields that I know are identical, and it will say "WARNING 003237: Join had no matches." Is anyone else having this trouble? When I run the same script in Python 2.7, the fields join. This happens whether I'm trying to join a csv to a feature class, fc to fc, or a gdb table to fc. 

8 Replies
DanPatterson
MVP Frequent Contributor

What does the Validate Join (Data Management)—ArcGIS Pro | Documentation

tool show?  

What are the contents of the fields?

pure text or numeric as text?

any potential leading/trailing spaces in one of the table fields?

left vs right justified "numbers"?  (first is text, second is probably a number)


... sort of retired...
LeviCecil
Occasional Contributor III

I got "WARNING 003237: Join had no matches" from validate join. 

Mary_Murphy
Esri Contributor

Are you trying to join to a .csv or other excel table?

LeviCecil
Occasional Contributor III

This happens whether I'm trying to join a csv to a feature class, fc to fc, or a gdb table to fc. Whatever I'm trying to join the table to, it doesn't work. There are a couple of null values in the key field, but that shouldn't matter, should it? 

DanPatterson
MVP Frequent Contributor

It does matter.  The only way to limit the problems are to use TableToTable or ExcelToTable to bring tabular data into Pro first prior to trying a join.  Nulls or any variant of missing data can convert numeric fields to text.  Tabular data also may contain leading trailing spaces which need to be properly parsed.  A trailing space won't be visible to you but will invalidate a join.  If you know there are nulls in the key field, you need to exclude those records at source.  There are other variants/issues that I haven't covered here, but in short... if the data are not pristine in the source or target tables, then complete or partial failed joins are the result.


... sort of retired...
LeviCecil
Occasional Contributor III

I do use table to table in my script to convert the csv into a geodatabase table. This is the input for the join fields tool. It still gets thrown by the nulls. It’s not a problem when I run the script in Python 2. What do you recommend as a solution? There is always the possibility of nulls with the amount of data I’m converting. 

DanPatterson
MVP Frequent Contributor

convert your nulls to an acceptable value at source.  I do this when I work with numpy and featureclasses.  For text it is simple just query for nulls and replace with text like "NONE", for integers, you can use some number like -999 etc

Try this on one of your tables to see what you get

def _make_nulls_(in_fc, int_null=-999):
    """Return null values for a list of fields objects, excluding objectid
    and geometry related fields.  Throw in whatever else you want.

    Parameters
    ----------
    in_flds : list of arcpy field objects
        Use arcpy.ListFields to get a list of featureclass fields.
    int_null : integer
        A default to use for integer nulls since there is no ``nan`` equivalent
        Other options include

    >>> np.iinfo(np.int32).min # -2147483648
    >>> np.iinfo(np.int16).min # -32768
    >>> np.iinfo(np.int8).min  # -128

    >>> [i for i in cur.__iter__()]
    >>> [[j if j else -999 for j in i] for i in cur.__iter__() ]
    """
    nulls = {'Double': np.nan, 'Single': np.nan, 'Float': np.nan,
             'Short': int_null, 'SmallInteger': int_null, 'Long': int_null,
             'Integer': int_null, 'String':str(None), 'Text':str(None),
             'Date': np.datetime64('NaT')}
    #
    desc = arcpy.da.Describe(in_fc)
    if desc['dataType'] != 'FeatureClass':
        print("Only Featureclasses are supported")
        return None, None
    in_flds = desc['fields']
    shp = desc['shapeFieldName']
    good = [f for f in in_flds if f.editable and f.name != shp]
    fld_dict = {f.name: f.type for f in good}
    fld_names = list(fld_dict.keys())
    null_dict = {f: nulls[fld_dict[f]] for f in fld_names}
    # ---- insert the OBJECTID field
    return null_dict, fld_names

... sort of retired...
Reply
0 Kudos
LeviCecil
Occasional Contributor III

Thanks. I'll give this a try. It is a text field, but the key is seven digit numbers. 

Reply
0 Kudos