Start your journey with Alteryx Machine Learning - Take our Interactive Lesson today!

Alteryx Machine Learning Discussions

Find answers, ask questions, and share expertise about Alteryx Machine Learning.
Getting Started

Start your learning journey with Alteryx Machine Learning Interactive Lessons

Go to Lessons
SOLVED

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

ghtill
7 - Meteor

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:

IDActiveJob CodeYear
25640Y195622015
25640Y986322020
15056Y195622015
15056Y195622016
15056Y195622018
15056Y562012020
15056N195622021


Expected Output

IDActiveJob CodeYear
25640Y195622015
25640Y986322020
15056N195622015
15056N195622016
15056N195622018
15056Y562012020
15056N195622021
4 REPLIES 4
DataNath
17 - Castor

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?

 

IDActiveJob CodeYear
25640Y195622015
25640Y986322020
15056N195622015
15056N195622016
15056N195622018
15056N562012020
15056N195622021
ghtill
7 - Meteor

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
17 - Castor

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:

 

DataNath_0-1660085887351.png

ghtill
7 - Meteor

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 👍