How to rank subsets of records in a table?

[pulsoft]

Hi,

I wonder if anyone could show me in Xbasic how to read through a table such as the example browse attached and rank the record entries by score within exam within date.
Does it require an additional table for temporary records?
I have indexed the table as I would require it displayed, as follows: DTOS(DATE)+ALLTRIM(EXAM)+PADL(ALLTRIM(STR(999-SCORE)),3,"0")
However, I have no idea how to write a batch script that would accomplish what I require with regards to ranking.
The attached browse also shows in the final colum the ranking I would expect to see after running the script.

Thanks in advance,

Paul

[Tom Cone Jr]

Re: How to rank subsets of records in a table?

Why not use a report, with multiple grouping levels?

If you must store the subgroup's "rank" in each record, you could use the following sequence:

pseudocode follows (better to enhance your learning experience!)

1) change the index spec: the desired sort sequence is by date, then by exam, then by INVERTED score. Why inverted? cause you want the top score (the highest score) to be #1 in each group, right?

2) open the table, set the revised index primary

3) fetch the first record

4) store the date + exam in a variable. This combination identifies the "current" subgroup, right? Let's call it the subgroup spec.

store the number 1 in a variable. this will be used to track the current rank number

insert the rank into the current record using a table change method, then increment the rank number

5) fetch through the records until the combination of date and exam changes. This tells you you've hit the first record in the next group. Make sense? Anyway, along the way, as you fetch through a subgroup change the rank number in each record and increment the rank variable as you go

6) when a new subgroup is encountered reset the rank var to 1 and set the variable containing the current subgroup spec to match the current subgroup spec; then repeat the dance. i.e. step through the next subgroup changing the rank field as you go, continue til the subgroup spec changes.

7) stop when you try to fetch past the last record

8) close the table on the way out.

This requires using nested loops.

For lots of examples using nested loops check out the xbasic programming puzzles forum here.

--tom

[Stan Mathews]

Re: How to rank subsets of records in a table?

Not sure I see the need for a script. Appears to be a simple update operation for the rank field.

Update expression

case(score <=15,1,score<45,2,score<99,.....

assuming your rankshouldbe for the score of 99 in the example is wrong.

[pulsoft]

Re: How to rank subsets of records in a table?

Thanks Tom and Stan, I will read and digest your replies and get back to you if I have any questions.

I must point out that I rushed putting this example table together and of course forgot that a score of 99 is greater than 80 or 60 or whatever and therefore should be ranked higher, please see revised screen grab and index.

Stan, the actual requirement I have is not for an exams database and the score field or related value could be one of a significantly larger and unknown range of values. So, if my cursory glance at your reply is correct in assuming you advocate a hard-coded update approach then I don't think that would work.

Having said that I need to look again at your reply.

Thanks again,

Paul

[Stan Mathews]

Re: How to rank subsets of records in a table?

the score field or related value could be one of a significantly larger and unknown range of values

You'd still have to have some kind of range of values that correlate with a specific rank. If your range of values and scores is larger than your example why not have a table with fields - upper score and rank. It would need to be in upper score order. Then run an update operation that looks up the person's score in the table and returns the rank.

[pulsoft]

Re: How to rank subsets of records in a table?

Many thanks to Tom,

His logic outline helped me to write a working script - see below - I also attach Tom's version which takes a different approach to the same result.

Thanks again Tom...

-------------------------------------------------------------------------------------------------------------------------------
'Date Created: 21-Aug-2011 02:16:04 PM
'Last Updated: 21-Aug-2012 06:40:56 PM
'Created By : Paul

dim vn_lastscore as n
dim vn_rank as n = 1
dim vd_date as d
dim vc_exam as c
vn_rank=999999

'open the demo exams table and set the index
t = table.open("demoexamanalysis",FILE_RW_SHARED)
t.index_primary_put("Bydateexamscore")
t.fetch_first()
if vn_rank=999999
vd_date=t.date
vc_exam=t.exam
vn_rank=1
vn_lastscore=t.score
end if
select
case t.date = vd_date .and. t.exam = vc_exam .and. t.score <> vn_lastscore
vn_rank=vn_rank+1
vn_lastscore=t.score
case t.date = vd_date .and. t.exam = vc_exam .and. t.score = vn_lastscore
vn_rank=vn_rank+0
case t.date <> vd_date .or. t.exam <> vc_exam
vd_date=t.date
vc_exam=t.exam
vn_lastscore=t.score
vn_rank=1
end select
t.change_begin(.t.)
t.rank=vn_rank
t.change_end(.t.)
t.fetch_next()

t.close()
-------------------------------------------------------------------------------------------------------------------------------------------------------
'Date Created: 21-Aug-2011 02:16:04 PM
'Last Updated: 21-Aug-2012 06:40:56 PM
'Created By : Tom Cone

dim vn_lastscore as n
dim vn_rank as n = 1
dim vd_date as d
dim vc_exam as c

'open the demo exams table and set the index
t = table.open("demoexamanalysis",FILE_RW_SHARED)
t.index_primary_put("Bydateexamscore")
t.fetch_first()

vd_date=t.date
vc_exam=t.exam
vc_curr_grp = dtos(vd_date) + vc_exam
vn_lastscore = t.score
while .not. t.fetch_eof()


while .not. t.fetch_eof() .and. dtos(t.date) + t.exam = vc_curr_grp
if vn_lastscore <> t.score then
vn_rank = vn_rank + 1
vn_lastscore = t.score
end if

t.change_begin()
t.rank = vn_rank
t.change_end(.t.)
t.fetch_next()

end while
vn_rank = 0
vc_curr_grp = dtos(t.date) + t.exam

end while
t.close()

[pulsoft]

Re: How to rank subsets of records in a table?

I realised later that my script didn't actually need the second case statement as it is superfluous to requirements...

Should be:

---------------------------------------------------------------------------------------------------------------
dim vn_lastscore as n
dim vn_rank as n = 999999
dim vd_date as d
dim vc_exam as c

'open the demo exams table and set the index
t = table.open("demoexamanalysis",FILE_RW_SHARED)
t.index_primary_put("Bydateexamscore")
t.fetch_first()
if vn_rank=999999
vd_date=t.date
vc_exam=t.exam
vn_rank=1
vn_lastscore=t.score
end if
select
case t.date = vd_date .and. t.exam = vc_exam .and. t.score <> vn_lastscore
vn_rank=vn_rank+1
vn_lastscore=t.score
case t.date <> vd_date .or. t.exam <> vc_exam
vd_date=t.date
vc_exam=t.exam
vn_lastscore=t.score
vn_rank=1
end select
t.change_begin(.t.)
t.rank=vn_rank
t.change_end(.t.)
t.fetch_next()

t.close()
----------------------------------------------------------------------------------------------------------------------------------

26 lines for Tom and 28 for me...

Paul

[pulsoft]

Re: How to rank subsets of records in a table?

Thinking about it, could we start a new forum for real life xbasic table processing examples. Nothing too complex, we don't want an excuse for other seasoned Alpha developers to write our code for us (thanks again Tom), but somewhere that is a good starting point for understanding the basics.
It took me over an hour yesterday to find in the Alpha documentation how to code the index you wish to process a table on.
We could maybe use an exams database as the basis for the examples.
E.G. Tables - Exams Master, Students, Student Disciplines, Discipline Exams, Terms (Semesters I think), Exam Timetable (Schedule), Exam Results, etc.
Some examples could be creating a schedule for a discipline, determining the average mark for an exam instance, determining the rank against previous exam instances, etc.
Just a thought,

Paul

[Mike Wilson]

Re: How to rank subsets of records in a table?

Paul,
I believe that is the intent of the AlphaSport database that Alpha supplies already. It is a reference that is commonly used to direct folks to for examlpes of how certain actions might be achieved.

Also,
your code seems to have a few irregularities.
1) if the first record in the table does not have a rank of 999999, then the variables cd_date, vc_exam and vn_rank will not have values entering the select-case selction, and none of the case statements would be satisfied.
2) if the first record in the table does have a rank of 999999, then vc_exam and vn_lastscore are defined in that section and since you have not moved to a different record, re-defining the value of those variables within the select-case section generates no change to the values that the variables already have.
3) typically, when looping through a table using t.fetch_first()-t.fetch_next(), this is included within a while .not. t.fetch_eof() and end while statements.

[pulsoft]

Re: How to rank subsets of records in a table?

Paul,
I believe that is the intent of the AlphaSport database that Alpha supplies already. It is a reference that is commonly used to direct folks to for examlpes of how certain actions might be achieved.

Also,
your code seems to have a few irregularities.
1) if the first record in the table does not have a rank of 999999, then the variables cd_date, vc_exam and vn_rank will not have values entering the select-case selction, and none of the case statements would be satisfied.
2) if the first record in the table does have a rank of 999999, then vc_exam and vn_lastscore are defined in that section and since you have not moved to a different record, re-defining the value of those variables within the select-case section generates no change to the values that the variables already have.
3) typically, when looping through a table using t.fetch_first()-t.fetch_next(), this is included within a while .not. t.fetch_eof() and end while statements.

Hi Mike,

Sorry for the delay in answering, have been out all day.

I appreciate your comment about Alpha Sports but I was talking about a more accessible and specific set of Xbasic data management examples.
I will design some tables, describe some data management examples and script them as best I can. If there are errors in my code I am hoping that Alpha vets here will correct them as I go.
These example scripts can then be made available for general reference by any Alpha Xbasic newbie, tagged under relevant categories, for example: read and update single table, read several tables and create spin off table, create summary table, delete records if certain conditions on linked tables exist, etc.

With regard to your comment about the script I wrote, I am not sure what you mean. It worked for me with no problems, producing exactly the same results as I expected and exactly the same results that Tom's script produced.

Thanks for the comments...

Paul

[Ray in Capetown]

Re: How to rank subsets of records in a table?

Thinking about it, could we start a new forum for real life xbasic table processing examples. Nothing too complex, we don't want an excuse for other seasoned Alpha developers to write our code for us (thanks again Tom), but somewhere that is a good starting point for understanding the basics.
It took me over an hour yesterday to find in the Alpha documentation how to code the index you wish to process a table on.
Paul

There's always place here for real life table processing and record manipulation, and lots of tips available. And many ways of achieving the result. I've found out some real nifty things useful for myself. Always willing to share.
Now you have found after an hour how to create the code for the desired index. Publish that here, and lets see what it is, and other possible ideas will emerge. Keeps it alive. I am for it.
I have been reading, understanding, learning from all of Stan and Tom's replies and comparing to my own thoughts on this thread. I'm trying however not to stand on anyones toes by adding spurious advice - that I tend sometimes to do.

[pulsoft]

Re: How to rank subsets of records in a table?

Paul,
I believe that is the intent of the AlphaSport database that Alpha supplies already. It is a reference that is commonly used to direct folks to for examlpes of how certain actions might be achieved.

Also,
your code seems to have a few irregularities.
1) if the first record in the table does not have a rank of 999999, then the variables cd_date, vc_exam and vn_rank will not have values entering the select-case selction, and none of the case statements would be satisfied.
2) if the first record in the table does have a rank of 999999, then vc_exam and vn_lastscore are defined in that section and since you have not moved to a different record, re-defining the value of those variables within the select-case section generates no change to the values that the variables already have.
3) typically, when looping through a table using t.fetch_first()-t.fetch_next(), this is included within a while .not. t.fetch_eof() and end while statements.

My Apologies Mike,

I copied and pasted the wrong script - the one above has while not EOF omitted... What a dummy... SB:

dim vn_lastscore as n
dim vn_rank as n
dim vd_date as d
dim vc_exam as c
vn_rank=999999

'open the demo exams table and set the index
t = table.open("demoexamanalysis",FILE_RW_SHARED)
t.index_primary_put("Bydateexamscore")
t.fetch_first()
while .not. t.fetch_eof()
if vn_rank=999999
vd_date=t.date
vc_exam=t.exam
vn_rank=1
vn_lastscore=t.score
end if
select
case t.date = vd_date .and. t.exam = vc_exam .and. t.score <> vn_lastscore
vn_rank=vn_rank+1
vn_lastscore=t.score
case t.date = vd_date .and. t.exam = vc_exam .and. t.score = vn_lastscore
vn_rank=vn_rank+0
case t.date <> vd_date .or. t.exam <> vc_exam
vd_date=t.date
vc_exam=t.exam
vn_lastscore=t.score
vn_rank=1
end select
t.change_begin(.t.)
t.rank=vn_rank
t.change_end(.t.)
t.fetch_next()
end while
t.close()

Paul

[pulsoft]

Re: How to rank subsets of records in a table?

Hi Ray,

The code is in the script:

t.index_primary_put("Bydateexamscore")

I just made sure that I had a suitable index on the table for processing:
DTOS(DATE)+ALLTRIM(EXAM)+PADL(ALLTRIM(STR(999-SCORE)),3,"0")
Instead of inverting the score field I subtracted from a hard coded value of 999 (I knew that none of my dummy data exceeded this value)

I attach a screen grab of the help page for indicating the primary index for processing.
What took me so long was locating this in the first place. I had no idea what tags to look under. This is my main beef with Alpha's help documentation. There is no logical indexing...
Hence my suggestion for some form of Xbasic basics forum. I know there is a forum for scripts and another that deals with puzzles, but I do not want to wade through pages of wide ranging topics in the hope of finding something pertinent to a mundane every day task that I haven't tackled in Alpha before...

Thanks for your post,

Paul