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
Solved! Go to 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!