ok, i have this in my .db

id | score | rating | slope
1 | 85 | 68.9 | 118
2 | 83 | 68.9 | 118
3 | 87 | 68.9 | 118
4 | 85 | 68.9 | 118
5 | 82 | 68.9 | 118
6 | 81 | 68.9 | 118
7 | 85 | 68.9 | 118
8 | 82 | 68.9 | 118
9 | 81 | 68.9 | 118
10 | 85 | 68.9 | 118
11 | 84 | 68.9 | 118
12 | 83 | 68.9 | 118
13 | 87 | 68.9 | 118
14 | 85 | 68.9 | 118
15 | 82 | 68.9 | 118
16 | 81 | 68.9 | 118
17 | 85 | 68.9 | 118
18 | 82 | 68.9 | 118
19 | 81 | 68.9 | 118
20 | 85 | 68.9 | 118
21 | 86 | 68.9 | 118
how can i do this:
1. find from the last/latest 20 records (in my case rounds, the range will be from ID 21 - ID 2)

2.pick the 10 lowest numbers (scores) from the 20 records

3. apply this formula ((score - rating) * 113 / slope * 0.96) to each of them
i.e.:
ID. 18 (82 - 68.9) * 113 / 118 * 0.96) = 12.0 (handicap)
ID. 19 (81 - 68.9) * 113 / 118 * 0.96) = 11.1 (handicap)
Until the 10th best scores...

4. do an AVERAGE on the "handicap"

thanks guys

If I were you, I'd add a field called handicap and calculate the handicap on every record (use javascript to calculate the total and insert it into the field when you add or modify a record).

Then you only need to worry about averaging the last 10 records. Plus you could pull your handicap over any given period of time.

Take a look at the category mod; it may give you some useful insight on grouping records.

Hey Watts, thanks for the reply.

After doing some searching on this forum and the faqs site. I found this mod below. I also added a new column to record "handicap" as you suggested.



Okay, the mod above is counting the ENTIRE column 5 but I only want to count up to the newest 20 records and do the AVERAGE on the 10 lowest records/handicaps.

How do I go about doing that?

Thanks

Anyone?

I'm at a total blank...

You'd have to add some kind of search range for dates and limit it to 20 records, then of those you'd have to sort it by score and take the first 10.

in the string for searching use mh (for max hits) and sb (for sort by).

You'd want mh=20 and sb=(whatever field number you want to sort on). From there I'm not sure what to do next...

thanks for the reply Watts.

ok, let say i have this .db

id | score | rating | slope | handicap
1 | 85 | 68.9 | 118 | 12.4
2 | 83 | 68.9 | 118 | 12.1
3 | 87 | 68.9 | 118 | 13.1
4 | 85 | 68.9 | 118 | 12.2
5 | 82 | 68.9 | 118 | 12.1
6 | 81 | 68.9 | 118 | 11.1
7 | 85 | 68.9 | 118 | 12.4
8 | 82 | 68.9 | 118 | 12.1
9 | 81 | 68.9 | 118 | 12.1
10 | 85 | 68.9 | 118 | 12.4
11 | 84 | 68.9 | 118 | 12.1
12 | 83 | 68.9 | 118 | 15.7
13 | 87 | 68.9 | 118 | 12.1
14 | 85 | 68.9 | 118 | 12.4
15 | 82 | 68.9 | 118 | 11.6
16 | 81 | 68.9 | 118 | 12.1
17 | 85 | 68.9 | 118 | 13.4
18 | 82 | 68.9 | 118 | 16.1
19 | 81 | 68.9 | 118 | 12.1
20 | 85 | 68.9 | 118 | 13.1
21 | 86 | 68.9 | 118 | 14.8

and i want to do this:

1. find the 10 lowest number in the 5th column (handicap). not from all the record!!! only from the lastest or rather last 20 records. so it would be something like this:
12.4
12.1
13.1
12.2
12.1
11.1
12.4
12.1
12.1
12.4

2. then do an average on it. so the final result should be something like
12.2


i thought i can kinda use the "Average Mod" (above on the 3rd posting) and extend on that mod.

anyone else out there can help????

sub get_average {
my $which_field = shift;
my $avg_count = 0; # Initialise the counter
my $avg_total = 0; # Initialise the total
open (DB, "<$db_file_name") or &cgierr("Unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
@lines = <DB>;
close DB;
my @lines = @lines[-20 ... -1]; #Last 20 rounds/records from .db
my @lines2;
@lines2 = sort { $a <=> $b} @lines; #Sort lowest to the highest from last 20 rounds/records
@lines2 = (@lines2[0 ... 9]); #10 lowest scores from the last 20 rounds/records
foreach $line (@lines2) {
chomp ($line);
@tmp_record = split (/\Q$db_delim\E/o, $line);
$avg_count++; # Count up each record
$avg_total = $avg_total + $tmp_record[$which_field]; # Add value to total
}
$avg_total = int $avg_total / $avg_count; # Average the total by number of records
print "$avg_total <br>";


Hey guys,
I think I am getting close to do what I am trying to do now with the above code. Only thing is I am actually SORTING the ID field/column instead the handicap field/column as I wanted. I will keep playing around with the code. If you guys experts out there know a quick mod. Please let me know.

Thanks

Hi guys,

I finally got it. thanks for reading this post. Here's the code. Hope that someone can reuse this for something else in the future.

Thanks


sub get_average {
my $which_field = shift;
my $avg_count = 0; # Initialise the counter
my $avg_total = 0; # Initialise the total
open (DB, "<$db_file_name") or &cgierr("Unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
@lines = <DB>;
close DB;
my @lines = @lines[-20 ... -1]; #Last 20 rounds/records from .db
my @sorted_lines = @lines;
my @tmp_record = ();
foreach (@sorted_lines) {
push @tmp_record,(split(/\|/))[3]; #trim everything but the 4th field/column
}
@tmp_record = sort {$a <=> $b} @tmp_record; #sort from lowest to highest
@tmp_record = @tmp_record[0 ... 9]; #10 lowest scores from the last 20 rounds
my $avg =0;
$avg += $_ for @tmp_record;
$avg /= 10;
print $avg;
}

Woo Hoo!

Feels good, doesn't it?

Congrats!