Multi Row look-up and flag

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

Workflow

Datasets

Expression

Accepted answers

JoeM

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

multirowexample.yxmd

JoeM

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

All comments

JoeM

@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

multirowexample.yxmd

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!

multirowexample_cc.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335