How to Join Tables with Different Features

902
5
Jump to solution
02-15-2022 07:00 PM
SarahModlin
New Contributor

How do I join these two data sets in ArcGIS? I'm trying to join the HeatingFuel table to USCounties by GEOID. I notice the last five digits in the GEOID field are the same, but I need a way to delete the beginning digits in the HeatingFuel table (or add them to the USCounties table). 

0 Kudos
1 Solution

Accepted Solutions
KimGarbade
Occasional Contributor III

Add a text field to your HeatingFuel table. Make it 10 wide (I guess anything more than 5).  Calc that field using an arcade expression something like Right($feature.GEOID, 5).  This should give you the common ID you need to create the join or relationship class.

The Right function will return the farthest right 5 characters from the field you enter into it. The downside of this is that it only works 100% if the ID you want to return is always the last 5 characters of the input string. If some of the IDs  are varying in length you might need to use a combo of the Find, Left, and Right functions. 

Also, I'm assuming the common IDs are text since they start with 0, and numeric data types hate that. 

View solution in original post

0 Kudos
5 Replies
KimGarbade
Occasional Contributor III

Add a text field to your HeatingFuel table. Make it 10 wide (I guess anything more than 5).  Calc that field using an arcade expression something like Right($feature.GEOID, 5).  This should give you the common ID you need to create the join or relationship class.

The Right function will return the farthest right 5 characters from the field you enter into it. The downside of this is that it only works 100% if the ID you want to return is always the last 5 characters of the input string. If some of the IDs  are varying in length you might need to use a combo of the Find, Left, and Right functions. 

Also, I'm assuming the common IDs are text since they start with 0, and numeric data types hate that. 

0 Kudos
SarahModlin
New Contributor

Thanks! But now my next problem is the fact that I can't add a text field to HeatingFuel it's read-only. How can I change that so I can add the text field?

0 Kudos
KimGarbade
Occasional Contributor III

That could be for a few reasons, but in this case I think it's because this is a .csv file. I would export the csv file to a table in a file geodatabase and add the field there. I guess you could also open the .csv file in Excel and add the column and perform the right function there (excel has its own Right function). Then re-add the .csv to your project. Keeping in mind that a .csv cannot be open in one program and edited in another at the same time  it will be locked, which could be your problem now  

0 Kudos
shan_sarkar
Occasional Contributor III

Where does the HeatingFuel table reside? 


~Shan
0 Kudos
SarahModlin
New Contributor

Nevermind my reply! I figured out how to edit my table! Thank you so so much!

0 Kudos