Multi Row look-up and flag

[Caleb]

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

[JoeM · Accepted 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.

[Caleb]

Thanks so much, Joe!

[Caleb]

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!

[JoeM · Accepted solution]

@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:

[Caleb]

Oh, you sure did! 

Thank you, much simpler than mine.