Can Alteryx Execute Conditional Multi-Row Changes that are by Groups?

[ghtill]

I am relatively new to Alteryx but have a little experience in data analysis. What I have in this example is two employees my goal is when ever there in a N in the active column that for every row above that with the same job code job code replace the Y with N grouped by the Employee ID. (the table is sorted by year in ascending order and grouped by ID). If anyone has a solution to this I would be very grateful. Thank you

Current Table:

ID	Active	Job Code	Year
25640	Y	19562	2015
25640	Y	98632	2020
15056	Y	19562	2015
15056	Y	19562	2016
15056	Y	19562	2018
15056	Y	56201	2020
15056	N	19562	2021

Expected Output

ID	Active	Job Code	Year
25640	Y	19562	2015
25640	Y	98632	2020
15056	N	19562	2015
15056	N	19562	2016
15056	N	19562	2018
15056	Y	56201	2020
15056	N	19562	2021

[DataNath]

Hey @ghtill, I'm a little confused here as the logic you have outlined doesn't seem to match up with the expected outcome, unless I'm missing something?

'when ever there in a N in the active column that for every row above that with the same job code job code replace the Y with N' seems to indicate that the penultimate row is incorrect and following should be the outcome, or am I overlooking a detail?

ID	Active	Job Code	Year
25640	Y	19562	2015
25640	Y	98632	2020
15056	N	19562	2015
15056	N	19562	2016
15056	N	19562	2018
15056	N	56201	2020
15056	N	19562	2021

[ghtill]

Hello @DataNath

Apologies if my initial logic was off or I wasn't clear but ID = Employee ID and the Job Code = unique code for job/responsibilities. The second to last row would not be a N = Not Active as the job code 56201 != 19562.

So because the current table has a N on the last row for ID = 15056 and Job Code = 19562 all rows above that meet this condition would Replace Y with N in the Active column. This represents that ID = 15056 still holds the job of job Code = 56201.  Hopefully this makes sense.

[DataNath · Accepted solution]

Ah I understand now, apologies - had been overlooking that indeed! How does this look? Works for your provided sample data but let me know if you run into any issues when applying it to a larger dataset:

[ghtill]

Thank you so much! Ill apply this to my larger dataset and if I run into any issues that I cant figure out ill reach out 👍