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