Join by incremented field

[spsanderson]

I have a field in my data called ROW_RECORD which is a repeating field of 1,2,1,2,...until the end of the file. I want to perform a join on a total of three columns and it would look like this:
PT_NO = PT_NO

FileName = FileName

ROW_RECORD = ROW_RECORD (but I want this to be ROW_RECORD - 1)

Any idea on how to achieve this? I know in SQL it is something simple like:

SELECT *
FROM TABLE_A AS A
INNER JOIN TABLE_B AS B 
ON A.PT_NO = B.PT_NO
  AND A.FileName = B.FileName
  AND A.ROW_RECORD = B.ROW_RECORD - 1

[DataNath · Accepted solution]

Hi @spsanderson, in Alteryx, we can't write expressions as part of a join condition - the tool acts strictly as <Field> = <Field>. As a result, I'd recommend that in your second input (TABLE_B in your example), you create a new, temporary field called something like JOIN_ROW_RECORD and just make it ROW_RECORD - 1. Then conduct the join on A.ROW_RECORD = B.JOIN_ROW_RECORD. One nice thing about the Join tool is that it has built-in Select functionality, so you could instantly remove this temporary field and it wouldn't show in the outbound records.

Hope this helps!