calculate ranks on the basis of marks everyday, which component?
Hi I perform aggregation using taggregate and then order by (marks) of students . Now i also have a ranking column in my output table. I need to output ranks of students based on the order by output , ie 1 to n ...... and this is done on a everyday basis, ie date is a key here. For a particular date, i assign ranks to students based on their performance on that day. This activity is repeated the next day. Thus , i need to know how i can do this. 1. Rank needs to be set to 1 whenever this job starts the next day. 2. From there , it needs to be incremented by 1 ................... and so on..... my job is attached.
should i use a tSetGlobalVar and add a key value pair: key="rank" value="1" And then write a routine which returns a incremented value of rank everytime it is called , and also resets this rank to incremented value. Can i do that in a routine ? And then when the job is done ,the tSetGlobalVar 's rank is resetted to 1 , so that when this aggregation starts for next day ....... what all ways wud u suggest to do this?
my input table: student id , marks, testId, testDate my o/p table: student id , rankForAParticularDate, testDate,testId , totalMarksOfAllTestsOFAStudent aggregation query: select student id, test date, testId , sum(marks) from <inputtable> group by studentId order by sum(marks) desc. Now i want to generate ranks for all students , on this particular testDate and populate the field rankForAParticularDate........ The highest marks students get the lowest rank (as in 1 , 2 and so on). So before sending the result of this query to my output table , i need to populate the rankForAParticularDate also. Starting from 1 .. i need to assign ranks to all students ..... as per their total marks in all tests conducted on a particular day. I need to start giving ranks from 1, everyday .... as thi sjob will be running continously ...........and will be scheduled everyday .........bby end of a partuclar day. d a key value pair: key="rank" value="1" And then write a routine which returns a incremented value of rank everytime it is called , and also resets this rank to incremented value. Can i do that in a routine ? And then when the job is done ,the tSetGlobalVar 's rank is resetted to 1 , so that when this aggregation starts for next day ....... what all ways wud u suggest to do this?
Hi aviator,
there are still some points not clear for me. I'll try to give you some "hints":
I use some testdata to make it clearer:
inputTable
aviator, 7, test-12, 28.08.
brehm, 4, test-12, 28.08.
aviator, 13, test-13, 29.08.
brehm, 13, test-13, 29.08.
aviator, 2, test-13, 29.08.
brehm, 1, test-13, 29.08.
aviator, 5, test-14, 29.08.
brehm, 3, test-14, 29.08.
Filter on 29.08.
After aggregation (aggregate all tests for the date) you'll get:
aviator, 29.08., 20
brehm, 29.08., 17
Now use a tMap and Numeric.Sequence to add a rank and write all in a temp file.
aviator, 29.08., 20, 1
brehm, 29.08., 17, 2
Now in a independent step read your file again and make a lookup to your temp file based on date and student id...
Hope this helps.
Bye
Volker
