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