How to create an offset column

[Silvex]

Hello,

For an N group of ID´s I need to test the expected behavior. I managed to do this in Excel using, in the Expected ID column, the Offset formula that is =OFFSET(B2,C2,0).

Group	Given ID	Offset	Expected ID	Result
Group 1	A	0	A	OK
Group 1	A	-1	A	OK
Group 1	B	0	B	OK
Group 1	A	-3	A	OK

I tried to do this using the multi-row formula however I have to enter a specific number or rows which in my case is dynamic. I´ve attached an Excel spread sheet with an example. The column I need to create is the Expected ID column.

Any help will be appreciated.

Thanks!

[MarqueeCrew]

could you assign a record id to each row then for the offset calculate the row that you are looking for? Then you'll u could join in the record id and the calculated row. Upon a join you will have the given Id.

[MarqueeCrew]

Calc row := RecordID + offset.

[jdunkerley79]

Agree with @MarqueeCrew suggestion.

Just a couple of extra steps I think, you need to use a summarise tool to get the first record id for a Group and Given ID. After that you can join back to the original data to create the offset. Something like:

SImple example attached.

[Silvex]

Thank you both  @MarqueeCrew and @jdunkerley79.

Now, what if I want to add the value based on the offset and given ID in the Expected ID (this will be a dinamic column) column? So I can get something like this:

Group	Given ID	Offset	Expected ID	Result
Group 3	E	0	E	OK
Group 3	F	0	F	OK
Group 3	F	-2	E	FAIL

[jdunkerley79]

Just to clarify the rules for the calculated values.

Should the offset be back to the first instance of Group, GivenID? i.e. in the case below would expect -1 for the last case.

You can use a formula tool to create both the Expected ID and Result, but need more details of the calculations before can give you guidance.

[Silvex]

I have an unknown amount of groups, a given ID that will be provided and an offset that I will be entering as well. I guess my question will be how to get the value of Expected ID based on the offset and the given ID?

Maybe the below image of what I did in Excel will help. I want to get column D.

I think I did not explain myself very well. Sorry about that. But this might help-

[jdunkerley79]

Makes perfect sense.

Ok so easiest is to add a RowCount and then we can use a formula tool to create the target RowCount to join to.

Having done that the Expected is an output of the joi n

And finally the result can be made from a formula tool.

Attached a sample

[Silvex]

Almost worked but the rows are off as you can see in the image below. The image on the right is Excel and the one from the left is Alteryx. The results are not quite the same. I try to fix it but not sure how.

Thank you

[jdunkerley79 · Accepted solution]

I had the join the wrong way round (hence all offsets becoming 0) and hadnt reset the order back to the input order.

So fliped the Join so Joins Target to Record (rather than Record to Target)

And added a sort by Record ID to reorder back into original order.

Updated workflow with some comments this time attached