Join Field changed the join table values.

695
6
Jump to solution
06-24-2021 12:33 AM
Labels (2)
JeromeWu
New Contributor II

Hello everyone. I am trying to join a field from a standalone table (PopWithin_FG) to a shapefile layer (FINAL). I used Add Attribute Index for both tables and then used Join Field. I am trying to add TRACT_FG to FINAL shapefile. 

After I run the tool, I noticed that the values in FINAL is completely different than the values in PopWithin_FG.

Eg. Observe FID_1*  114, the value for TRACT_FG in PopWithin_FG is completely different than TRACT_FG in FINAL. Does anyone know why this is happening? Is there a workaround for this issue?

 

Thank you.

0 Kudos
1 Solution

Accepted Solutions
Nicole_Ueberschär
Esri Regular Contributor

Can you check if your PopWithin_FG table has duplicates in the FID_1 field?

The result looks to me like there are three rows in PopWithin_FG with the value 114 in the FID_1 field. When joining the field it takes only the first row it finds (with the value 0.403064), when doing the Add join it joins all rows, creating duplicates of the Input row. Maybe try to sort the Pop table by FID_1 and see if you have duplicates here. If the duplicates are correct from the data side (meaning you need those in the Pop table), I would try to find a way to query those rows you want to write the values for into the Final and then do the join.

View solution in original post

6 Replies
Nicole_Ueberschär
Esri Regular Contributor

Hello @JeromeWu,

Which field were you using to join the TRACT_FG to FINAL? Is it possible that you accidentally used the wrong ID, e.g. the default setting?

Feel free to share the data with me to see if I can reproduce the issue.

0 Kudos
JeromeWu
New Contributor II

Sorry, I'm not sure what you mean by field in this context. Here is the parameters that I used for the Join Field tool though. Did I input it incorrectly? 

Thank you!

0 Kudos
Nicole_Ueberschär
Esri Regular Contributor

I meant the Input Join Field and Joined Table Field in the tool settings but they look correct to me. Strange.

Could you try to Add join instead of Join Field to see if that brings the correct result?

 

 

0 Kudos
JeromeWu
New Contributor II

So I tried Add Join earlier and it kind of worked. It created three records for FID_1 114 in FINAL (I highlighted the three records in FINAL table). The first record is the correct one but the following two records is not what I need, not sure where it pulled those TRACT_FG values as well. I would use this solution, however, I feel like  it would be pretty unproductive to go through 1200+ records and delete the incorrect ones. Do you know of any workaround for this?

0 Kudos
Nicole_Ueberschär
Esri Regular Contributor

Can you check if your PopWithin_FG table has duplicates in the FID_1 field?

The result looks to me like there are three rows in PopWithin_FG with the value 114 in the FID_1 field. When joining the field it takes only the first row it finds (with the value 0.403064), when doing the Add join it joins all rows, creating duplicates of the Input row. Maybe try to sort the Pop table by FID_1 and see if you have duplicates here. If the duplicates are correct from the data side (meaning you need those in the Pop table), I would try to find a way to query those rows you want to write the values for into the Final and then do the join.

JeromeWu
New Contributor II

Looks like you were right. Seems like I had some issues with my Tabulate Intersection analysis earlier, I fixed it and it is working now.

Thank you!