CODE 10% OF A DATA FILE BASED ON TEST SCORE

[wilsong21]

I AM USING VFP 6.0
I have a data file with a Character field called TESTSCORE it contains whole number 100,99,98,97,96.....0
I would like to sort it in descending order by TESTSCORE then code in a different field

A - for the top 10%
B - for NEXT 10%
C - for NEXT 10%
D - for NEXT 10%
E - for NEXT 10%
...J. UNTIL IT CODES 100% OF THE FILE WHICH WOULD GIVE 10 CODES A,B,C,D,E,F,G,H,I,J
HOW DO I GO ABOUT DOING THIS.

I KNOW I WILL NEED TO USE THE AVG FUNCTION BUT DON'T KNOW HOW TO PUT IT TOGETHER.

 

[bborissov]

INDEX IN STR(100-VAL(TestScore))+OtherField TAG ....

But then if you use this index for seek you must use:
SEEK (100-something) to get the right record.

Borislav Borissov

 

[alvechurchdata]

I'm not sure what you mean by 10%. Do you mean:

One tenth of the students get "A", one tenth get "B" etc

or

Students scoring between 91% and 100% get "A", those scoring between 81% and 90% get "B" etc?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[wilsong21]

YES alvechurchdata, I need to look at the entire score in the data file and code the top 10%/(1/10) of records based on score then code the next 10%/(1/10) records of the data based on score. Does this help?

 

[JimWWinter]

How do you want to handle situations where 2 scores are identical but including both in a given percentile would make it greater than 10% of the total? For example, lets say the top 6 scores out of 50 are 95, 95, 95, 94, 93, and 93.

Regards,
Jim

 

[wilsong21]

OK AFTER THINKING ABOUT IT
I want to sort the file by highest to lowest(descending) score then code the top 10% records. The 10% is based on record number not on the score. Then code next tenth B,C,D...etc

 

[TamarGranor]

USE YourTable
INDEX ON TestScore TAG TestScore && only need to do this once

nRecords = RECCOUNT("YourTable")
nTenPercent = FLOOR(nRecords/10)
cCurCode = "A"
nRankCount = 0

SCAN
  IF nRankCount = nTenPercent
     * done this rank
     cCurCode = CHR(ASC(cCurCode) + 1)
     nRankCount = 0
  ENDIF

  REPLACE cRank WITH cCurCode IN YourTable
  nRankCount = nRankCount + 1
ENDSCAN

This should work (it's untested) as is if you have a number of records divisible by 10. If not, you need to figure out what you want to do with the extras and adjust the code.

Tamar