Hello Everyone,
I'm hoping someone can help me figure out how to create a Alteryx process that flags a row when certain conditions are met within a series of rows containing the same unique identifier.
I created a small table below that shows the structure of my data. The table contains historical student enrollment. Each enrolled student has one row per year they were enrolled. Usually, a student's grade level increases by one as the years pass. However, in some cases, student's are retained (repeat a grade). I'm trying to figure out how to flag the repeated grade(s) for these students.
School_Year | StudentID | Grade_Historical |
2012-2013 | 99999 | 10 |
2013-2014 | 99999 | 10 |
2014-2015 | 99999 | 11 |
2012-2013 | 11111 | 1 |
2013-2014 | 11111 | 2 |
2014-2015 | 11111 | 3 |
Thank you in advance!
Caleb
Solved! Go to Solution.
@Caleb - you were right on with the idea of using a multi-row formula. Attached a solution using your sample data.
(Disregard the dynamic rename as a part of the process - I was just renaming the field from copying it over from your Community post)
Formula:
IF [Grade_Historical]=[Row-1:Grade_Historical] then 1 else 0 endif
AKA - if the row above representing the previous grade shows the exact same grade at the current record level, flag it. Note that it is important to have a sort process before the multi-field formula (year asc, grade asc) and to group by student in the multi-field formula.
Thanks so much, Joe!
Adding to this for anyone who intends to use this solution.
JoeM's attachment works, but when two different students share a common grade in contigious rows the second student will be flagged as retained. This is not what we want.
Shown here:
School_Year | StudentID | Grade_Historical | Retained Flag |
2012-2013 | 99999 | 10 | |
2013-2014 | 99999 | 10 | R |
2014-2015 | 99999 | 11 | |
2012-2013 | 11111 | 1 | |
2013-2014 | 11111 | 2 | |
2014-2015 | 11111 | 3 | |
2012-2013 | 222222 | 3 | R |
2013-2014 | 333333 | 2 | |
2014-2015 | 333333 | 3 |
So, to solve this I created a seperate branch that creates a null() Grade_Historical along with the Max Year for each student. When these are unioned and sorted it creates a barrier record between groups of student records so the above won't happen. After you've run your Multi-row formula you can filter out the nulls.
Looks like this
School_Year | StudentID | Grade_Historical | Retained Flag |
2012-2013 | 99999 | 10 | |
2013-2014 | 99999 | 10 | R |
2014-2015 | 99999 | 11 | |
2014-2015 | 99999 | Null | |
2012-2013 | 11111 | 1 | |
2013-2014 | 11111 | 2 | |
2014-2015 | 11111 | 3 | |
2014-2016 | 11112 | Null | |
2012-2013 | 222222 | 3 | |
2012-2013 | 222222 | Null | |
2013-2014 | 333333 | 2 | |
2014-2015 | 333333 | 3 | |
2014-2016 | 333333 | Null |
I don't know if this is the most efficent way of doing this but it seems to work. Workflow is attached.
Thanks again, Joe!
@Caleb thanks for sharing your workaround! I made mention of a feature above, but probably should have highlighted the functionality more. If you select a field the student id field to 'group by' in the multi-row formula, it would only perform the analysis per student. This would prevent the grade retention carry-over from student ID to student ID. Note the config here:
Oh, you sure did!
Thank you, much simpler than mine.