Hi,
I'm looking for a way to go back X rows in the multi-row formula based on a value in the data table.
For example, the [Duration] is an integer 70 in one row, I want to reference 70 rows back, instead of a constant -1 or -2 rows.
Thanks!
Donny
Solved! Go to Solution.
I would add Record ID tool to add row ID to each record. Then I would use a Select Record tool and select records from row 71 onwards in a separate data stream. Now I would join the two data stream like row id = row id+70 and do rest of the operations. Row ID 71 (in second data stream) and .row ID 1 (in first data stream) are in the same row now after the above join.
What about a slightly different approach. You can use a Record ID followed by a formula tool that says [Record ID]-[Duration] which will result in the column ID to be referenced, right? Then you could follow that up with a join tool that joins the data to itself with [Column to Reference] joined to [Record ID]. This would pull the corresponding row data that is needed. Then you could do whatever calculation needs done with the newly linked up data.
I have attached a workflow as reference
@rituraj963 it looks like we have a similar idea!🙂
Thank you! This worked great.
@BrandonB Your example was EXACTLY what I needed.
@jerewatson happy to help and one of my favorite reasons why the Alteryx Community exists :)