topic Re: Join Field Not Working in ArcGIS Pro Questions

Join Field Not Working

LeviCecil — Wed, 23 Dec 2020 21:28:11 GMT

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.

Re: Join Field Not Working

DanPatterson — Wed, 23 Dec 2020 23:06:12 GMT

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)

Re: Join Field Not Working

Mary_Murphy — Thu, 24 Dec 2020 05:29:51 GMT

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

Re: Join Field Not Working

LeviCecil — Thu, 24 Dec 2020 22:51:53 GMT

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

Re: Join Field Not Working

LeviCecil — Thu, 24 Dec 2020 22:52:47 GMT

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?

Re: Join Field Not Working

DanPatterson — Fri, 25 Dec 2020 14:00:37 GMT

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.

Re: Join Field Not Working

LeviCecil — Sun, 27 Dec 2020 16:01:19 GMT

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.

Re: Join Field Not Working

DanPatterson — Sun, 27 Dec 2020 18:56:55 GMT

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

Re: Join Field Not Working

LeviCecil — Mon, 28 Dec 2020 16:24:46 GMT

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

Re: Join Field Not Working

LeviCecil — Thu, 15 Jul 2021 15:29:59 GMT

Joining is still a problem in Pro 2.8

Re: Join Field Not Working

SGuiryVR — Tue, 05 Oct 2021 18:12:10 GMT

I get the same issue, even though I performed calculate field based on a .csv table based on an ID # value, then when I remove join and try to join again based on the calculated field it says the join had no matches

Re: Join Field Not Working

Spatialelement — Tue, 11 Jan 2022 03:01:50 GMT

Has this issue been resolved? I'm currently trying to perform a very simple join in ArcGIS Pro V2.9.0 and am getting the same "WARNING 003237: Join had no matches". This warning ID doesn't seem to appear in any help documentation other than this thread.

I've checked the raw data for any blank values etc. I've indexed my join fields. I've converted my incoming CSV to a table stored in my projects default geodatabase. Nothing seems to be working.

Re: Join Field Not Working

skyhook — Wed, 02 Feb 2022 16:55:07 GMT

also suffering from this bug. same as described above. taken all previously mentioned steps & attempting to join 2 file geodatabase tables. Shortly before tool finishes it's run I see "0 matches" & i get NULL output. However, history & processing details just show operation succeeded with no warnings or errors. This is a huge disruption in workflow. reminds me of the common joke: funny how arcmap is more useful than ever before now that it's end-of-life & not updated any longer.

Re: Join Field Not Working

shan_sarkar — Thu, 03 Feb 2022 04:24:35 GMT

That is definitely weird, however can you check if there are any extra spaces before or at the end of the fields you are trying to join. If there are extra spaces in the values then arcgis will fail to see them as the same or identical values as it compares the values character by character.

Re: Join Field Not Working

ArsenBeglaryan — Tue, 08 Feb 2022 06:28:21 GMT

***UPDATE***

I have done some tests today shortening paths etc. and that was not the issue. The cause of this was one row manually selected and forgotten in my 1200 row input table. For that reason the Join Field GP tool was returning no matches. It started working properly as soon as I unselected the row in the input table, no queries were applied. Working on ArcGIS Pro 2.9.1

Any updates on the issue?
It is already becoming a bit "not funny" at all. Having the same issue on 2.9, losing tons of time having to perform this simple task on an ArcMap, QGIS or other tools.
It works one time and keeps on failing other times in ArcGIS Pro with same parameters, either Warning 003237 or tool finding 1 match only.
I have no nulls, spaces etc., having a "table to table" table in the default GDB, that already worked one time and kept on failing other times with same parameters.

Only other potential solution I am thinking of, is the long path issue on Windows machines. I had this type of weird errors in other GP tools that got solved shortening the names and paths etc., but did not try in this case.

Re: Join Field Not Working

LeviCecil — Mon, 14 Feb 2022 19:33:35 GMT

Here's a fun new wrinkle in Pro 2.9.1. Lately I've been successfully joining from Excel sheets. The other day I added new fields to a Sites points dataset and calculated values from an Excel sheet using the Site ID field as the join. I found out this morning that the Site ID field in around 45 of our sites was deleted during this process. They were nulls. This messed up the functionality of our facilities dashboard app. I had to go in and add the Site IDs back in. This is a real problem.

Re: Join Field Not Working

JosephAlberts — Wed, 06 Apr 2022 19:50:52 GMT

I was just running into this problem myself on Pro 2.9.2.

Basic steps were Table to Geodatabase from CSVs prepared for me, attempt join from a hosted feature layer added to the map to the new table. I was joining from the GlobalID of the hosted feature layer (type: GlobalID) to the GlobalID in the table (type: string, length: 255).

If I reversed the join (i.e, table to hosted feature layer, same join field), everything was fine.

It seems like my problem, in this case, was that I had a definition query applied to the hosted feature layer. When I turned it off, I was able to get a correct match count when validating the join from the hosted feature layer to the table.

That's definitely not the behavior I would expect though.

Re: Join Field Not Working

ColinHLang — Mon, 10 Jul 2023 14:36:05 GMT

I have been seeing this recently in ArcGIS Pro 2.9.7. Two tables will not join, especially when the join field is a text field. I can visibly see the matches, but the software can't find them. No leading or trailing zeros or spaces. Some null records, so that is one thing I'll have to investigate, but I've also found I have better results often after I index the field on both sides of the join. Why it would fail without an index and succeed with it is vexing me. I would expect it to just take longer, not fail completely. Joins definitely work a lot better in ArcMap than they do in Pro, and that should not be the case. This is some of the most basic functionality of GIS.

Re: Join Field Not Working

Kalgul — Mon, 28 Aug 2023 01:21:01 GMT

I have aged about 10 years in the past few workdays, spending about 15 man-hours trying to get past this ONE problem which has completely stopped my workflow in a career-defining project. Selecting by attribute confirms the matching entries for a 1:M join, but it just will not join, no matter what I do. I have the shapefile, I have the csv, I have both of them exported to a geodatabase, and nothing I do has gotten past the cursed 'WARNING 003237: Join had no matches.' error.

Re: Join Field Not Working

LeviCecil — Mon, 28 Aug 2023 20:03:14 GMT

If you have access to the Data Interoperability extension for Pro, I would highly recommending using it for joins and attribute management in general. It's basically a lite version of Safe FME software, which is basically a really advanced version of model builder. It does these sorts of tasks so much better than ArcGIS Pro. I'm moving some of my processes out of Python/ArcPy into FME and it's very effective.