Moving beyond noob queries (comparing results)

[awmorris]

Imagine the following data set:

STUDENT	EOY_GRADE	GENDER	STUDENT_STATUS
Alice	96	Female	ACTIVE
Bob	94	Male	ACTIVE
Candice	92	Female	FORMER
Debbie	94	Female	FORMER
Eddie	94	Male	FORMER
Frank	96	Male	FORMER

And I would like the produce the following output comparing current students to former:

STUDENT	EOY_GRADE	PREV_GENDER_AVG	PREV_CLASS_AVG	CURRENT_CLASS_AVG
Alice	96	93	94	95
Bob	94	95	94	95

Thanks in advance for consideration and thoughts

[awmorris]

To help, here is an SPL query to preload the data:

| makeresults
| eval STUDENT="ALICE" |eval EOY_GRADE=96 |eval	GENDER="FEMALE"	|eval STUDENT_STATUS="ACTIVE"
| append [ makeresults  | eval STUDENT="BOB" |eval EOY_GRADE=94	|eval GENDER="MALE"	|eval STUDENT_STATUS="ACTIVE"]
| append [ makeresults  | eval STUDENT="CANDICE" |eval EOY_GRADE=92	|eval GENDER="FEMALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="DEBBIE" |eval EOY_GRADE=94	|eval GENDER="FEMALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="EDDIE" |eval EOY_GRADE=94	|eval GENDER="MALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="FRANK" |eval EOY_GRADE=96	|eval GENDER="MALE"	|eval STUDENT_STATUS="FORMER"]
|table STUDENT,EOY_GRADE,GENDER,STUDENT_STATUS

[awmorris]

Getting closer... but still no dice:

| makeresults
| eval STUDENT="ALICE" |eval EOY_GRADE=96 |eval	GENDER="FEMALE"	|eval STUDENT_STATUS="ACTIVE"
| append [ makeresults  | eval STUDENT="BOB" |eval EOY_GRADE=94	|eval GENDER="MALE"	|eval STUDENT_STATUS="ACTIVE"]
| append [ makeresults  | eval STUDENT="CANDICE" |eval EOY_GRADE=92	|eval GENDER="FEMALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="DEBBIE" |eval EOY_GRADE=94	|eval GENDER="FEMALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="EDDIE" |eval EOY_GRADE=94	|eval GENDER="MALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="FRANK" |eval EOY_GRADE=96	|eval GENDER="MALE"	|eval STUDENT_STATUS="FORMER"]
| stats list(STUDENT) AS STUDENTS,list(GENDER) AS GENDERS,list(eval(if(GENDER="MALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,""))) as MALE_GRADES, list(eval(if(GENDER="FEMALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,""))) as FEMALE_GRADES,list(eval(if(STUDENT_STATUS="FORMER",EOY_GRADE,""))) as PREVIOUS_GRADES,list(eval(if(STUDENT_STATUS="ACTIVE",EOY_GRADE,""))) as CURRENT_GRADES by STUDENT_STATUS

[awmorris]

HIT DANG!!!   I FINALLY GOT IT!

| makeresults
| eval STUDENT="ALICE" |eval EOY_GRADE=96 |eval	GENDER="FEMALE"	|eval STUDENT_STATUS="ACTIVE"
| append [ makeresults  | eval STUDENT="BOB" |eval EOY_GRADE=94	|eval GENDER="MALE"	|eval STUDENT_STATUS="ACTIVE"]
| append [ makeresults  | eval STUDENT="CANDICE" |eval EOY_GRADE=92	|eval GENDER="FEMALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="DEBBIE" |eval EOY_GRADE=94	|eval GENDER="FEMALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="EDDIE" |eval EOY_GRADE=94	|eval GENDER="MALE"	|eval STUDENT_STATUS="FORMER"]
| append [ makeresults  | eval STUDENT="FRANK" |eval EOY_GRADE=96	|eval GENDER="MALE"	|eval STUDENT_STATUS="FORMER"]
| eval MALE_GRADE=if(GENDER="MALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,"")
| eval FEMALE_GRADE=if(GENDER="FEMALE" AND STUDENT_STATUS="FORMER",EOY_GRADE,"")
| eval PREVIOUS_GRADE=if(STUDENT_STATUS="FORMER",EOY_GRADE,"")
| eval CURRENT_GRADE=if(STUDENT_STATUS="ACTIVE",EOY_GRADE,"")
| eval STUDENT_STRING=STUDENT.",".EOY_GRADE.",".GENDER.",".STUDENT_STATUS
| stats avg(CURRENT_GRADE) AS CURRENT_CLASS_AVG, avg(MALE_GRADE) AS PREV_MALE_AVG, , avg(FEMALE_GRADE) AS PREV_FEMALE_AVG, , avg(PREVIOUS_GRADE) AS PREV_CLASS_AVG,list(STUDENT_STRING) AS STUDENTS
| mvexpand STUDENTS
| search STUDENTS="*,ACTIVE"
| rex field=STUDENTS  "(?<STUDENT>.*),(?<EOY_GRADE>.*),(?<GENDER>.*),(?<STUDENT_STATUS>.*)"
| eval PREV_GENDER_AVG=if(GENDER="MALE",PREV_MALE_AVG,PREV_FEMALE_AVG)
| table STUDENT,EOY_GRADE,PREV_GENDER_AVG,PREV_CLASS_AVG,CURRENT_CLASS_AVG