How to join more than one fields in feature class?

991
5
03-17-2021 09:13 AM
AlexP_
by
Regular Contributor

I have arcgis pro 2.7.1 and sde.

How do I join more than one fields in feature class and table? I see it is allowed only one field from join. Please advise.

Thank you

Alex

0 Kudos
5 Replies
RichardFairhurst
MVP Honored Contributor

What is the ultimate purpose of the join?  Is the join primarily being used for transferring data between the feature class and table, creating feature labels, creating layer symbology, or expanding the feature records to show all of the combinations in a one-to-many or many-to-many relationship with the table.  Is the data relationship supposed to refresh in real time as you edit the features and/or the table and maintain performance in a real time map?  Without knowing your specific needs, all I can do is offer a few suggestions and my feeling about their usefulness.

The Make Query Table tool can do multi-field joins, but it is not editable, it only shows records that match, it doesn't really refresh and performs badly for large dataset joins.  For real time needs, I would say it is best to concatenate the values of multiple fields into a single field with a delimiter character separator and use that field to do a standard single field join.  The concatenated field can be maintained in real time for both the feature class and the table using a field calculation like expression using Attribute Assistant in an Editor session under ArcMap Desktop and in Pro with Attribute Rules, provided your data is in a geodatabase that you can customize.

If the multi-field join is being used for doing Geoprocessing data transfer operations, see the Creating a Multi-Field Python Dictionary Key to replace a Concatenated Join Field section in my Turbo-Charging Data Manipulation with Python Cursors and Dictionaries Blog.

Since your data is in SDE, perhaps creating a view using the underlying database would be your best bet, although I have no real experience in setting up enterprise database views.

AlexP_
by
Regular Contributor

Hello Richard,

 

Thank you for the information. This data has about 40k and continue to increase. Since these both feature class and table have no unique identifier. The goal here is to join both three fields to be accurate then the fourth field will be transfer or input into the feature class from a table because this feature class doesn't have the fourth field info as needed. this is need to be update weekly.

0 Kudos
RichardFairhurst
MVP Honored Contributor

Since the purpose of your join is to transfer data from the table to the feature class, my Blog applies.  If you change the lines of code below based on the comment that precedes them to match your path, table/feature class names, and field names, this code should work to transfer the data.  This code is not set up for versioned SDE data or SDE data that requires an Editor session to make changes, but it could be adapted to deal with that if needed.  This code assumes the concatenation of the 3 fields uniquely identifies each row in the table.  If the concatenated values of the three fields in the table are not unique, only the value from the last table record holding the concatenated value from the three fields will be passed to all of the matching features in the feature class.  The code could be modified to check for concatenated keys in the source table that are not unique and do statistical operations on the value being transferred, like First, Last, Min, Max, Sum, Mean, etc. (depending on the data type of the value being transferred).

 

 

 

from time import strftime

print( "Start script: " + strftime("%Y-%m-%d %H:%M:%S"))

import arcpy

## Change the path and source table name to match your table
sourceFC = r"C:\Path\SourceTable"

## Change the names of the three join fields and the fourth value field
sourceFieldsList = ["JoinField1", "JoinField2", "JoinField3", "ValueField"]

# Use list comprehension to build a dictionary from a da SearchCursor where the key values are based on 3 separate feilds
valueDict = {(r[0],r[1],r[2]):r[3:] for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

## Change the path and update feature class name to match your feature class
updateFC = r"C:\Path\UpdateFeatureClass"

## Change the names of the three join fields and the fourth value field
updateFieldsList = ["JoinField1", "JoinField2", "JoinField3", "ValueField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable
        keyValue = (updateRow[0],updateRow[1],updateRow[2])
        # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
            # transfer the value stored under the keyValue from the dictionary to the updated field.
            updateRow[3] = valueDict[keyValue]
            updateRows.updateRow(updateRow)

del valueDict

print( "Finished script: " + strftime("%Y-%m-%d %H:%M:%S"))

 

 

 

 

 

 

0 Kudos
AlexP_
by
Regular Contributor

@RichardFairhurst  Thank you. It looks like I add this script in ArcGIS Pro python window? 

0 Kudos
RichardFairhurst
MVP Honored Contributor

Press the Windows button on your computer (lower left corner of the Windows task bar), press the ArcGIS application group, then press the Python Command Prompt application to open a command prompt window.  Type Idle and press Enter to start Idle (Python GUI application).  From the File menu create a New File or use the shortcut Ctrl-N.  Paste the code into the file.  From the File menu Save (Ctrl-S shortcut) the file in the directory you want.  Give the file the name you want and make sure to put the .py extension at the end.  You can run the file from the Run menu Run Module (F5 shortcut).  If there are errors they will be shown in the main Idle window.

You can use this .py file to schedule a weekly task using the Windows Task Manager that will run automatically at the date and time you specify.  For the program script portion of the action use (keep in double quotes and replace yourusername with your actual user name):

"C:\Users\yourusername\AppData\Local\Programs\ArcGIS Pro\bin\Python\envs\arcgispro-py3\pythonw.exe"

For the Add arguments put a double quoted string containing the path and file name of your python script.