0

Let's say I have two time series shapefiles and I want to join them based on the common values in ID (not ArcGIS created ID) and Date column. Currently I am using join via table attributes, but the output is messed up as the values don't align based on both ID and Date, just ID.

How can I do this in ArcMap 10.7?

Sample data 1

ID X  Y  Date     Var1
a  X1 Y1 1/1/2020 1
a  X1 Y1 1/2/2020 2
b  X2 Y2 1/1/2020 3
b  X2 Y2 1/2/2020 4

Sample data 2

ID X  Y  Date     Var2
a  X1 Y1 1/1/2020 5
a  X1 Y1 1/2/2020 6
b  X2 Y2 1/1/2020 7
b  X2 Y2 1/2/2020 8

Incorrect output using table attribute join based on ID:

ID X  Y  Date     Var1 Date     Var2
a  X1 Y1 1/1/2020 1    1/1/2020 5 
a  X1 Y1 1/2/2020 2    1/1/2020 5
b  X2 Y2 1/1/2020 3    1/1/2020 7
b  X2 Y2 1/2/2020 4    1/1/2020 7

Desired output

ID X  Y  Date     Var1 Date         Var2
a  X1 Y1 1/1/2020 1    1/1/2020     5
a  X1 Y1 1/2/2020 2    1/2/2020     6
b  X2 Y2 1/1/2020 3    1/1/2020     7
b  X2 Y2 1/2/2020 4    1/2/2020     8
5
  • Does this answer your question? Creating join based on multiple fields using ArcGIS Desktop?
    – Taras
    Nov 11 '21 at 7:08
  • @Taras, the answer of that question puts all the column values in a single row, which is not what I want. Plus I am not that familiar with arcpy as I use R, so I am looking for an interactive way of doing this in ArcMap. Nov 11 '21 at 7:13
  • @Taras, I just also tried Make Query Table, but under Key Fields, the date column is not showing for selection, otherwise I cloud have used a combination of fields (ID and Date) that can be used to uniquely identify a row in the query. Nov 11 '21 at 7:42
  • Maybe convert a column with date into a column as a string type.
    – Taras
    Nov 11 '21 at 7:43
  • I used FID as the common field since the number of records in both the tables are the same, so it solved the problem as now the correct values correspond to the correct ID and Date since FID are created in sequence from 1st to last value. However there might be a better way of doing this in ArcMap. Nov 11 '21 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.