initial looks

[gandalf380]

I worked AQU over the weekend with no problems. i am having issues with the database however. I was able to import the files into the Excel worksheet. However I can not get the filter to work. Nothing happens when I change the filter parameters. The worksheet shows 13 files in the total but only shows a count of one. That does not change no matter how I change the filter. Any suggestions?

[Ted Craven]

Originally Posted by gandalf380

I worked AQU over the weekend with no problems. i am having issues with the database however. I was able to import the files into the Excel worksheet. However I can not get the filter to work. Nothing happens when I change the filter parameters. The worksheet shows 13 files in the total but only shows a count of one. That does not change no matter how I change the filter. Any suggestions?

Charlie - I could not duplicate that issue. I sent you an email inviting you to a remote Zoom meeting to demo what you're seeing.

The process is (as always before) - make a copy of the new Model Worksheet found in the c:\rdss22\Models\Templates\ folder (named: Model.Database.2025-V1.2 (BLANK Template - Make a Copy!.xlsm) and move it to the main Models folder - rename it anything, but perhaps keep the 'Model.Database.2025-V1.2' portion of the name for tracking different format versions when the structure gets updated from time to time.

Choose that file when RDSS prompts you which Excel Model Database file to import into, then use the Import Data! button on the main Data sheet.

Sounds like tha'ts what you did, but no results on the various Filter sheets ...
��

Ted

[gandalf380]

I have worked four days of races now. Sat and Sun for AQU and Tues and Wed for CD. If I make a database for just CD then everything works perfectly. As soon as I add the two AQU days the filter goes haywire. So something is wrong with the AQU files. I will try AQU tomorrow and see what happens.

[gandalf380]

i think I figured out the answer. The problem occurs when you import a data file that is missing PL and BPP. When these are missing, the file does not import properly into the database.

[Ted Craven]

Originally Posted by gandalf380

i think I figured out the answer. The problem occurs when you import a data file that is missing PL and BPP. When these are missing, the file does not import properly into the database.

Charlie, yes indeed, I think that is the issue - thanks. I tested on tomorrow's KY Derby race, while TS Ratings are all blank/missing. I will modify the filter formulas so that data is retrieved even if those ratings are missing.


Ted

[Ted Craven]

Charlie - if you are comfortable enough in Excel to replace the existing retrieval formula in both the Filter Example and Leverage/Pace Sheets - following is a replacement formula which will return data from lines which have no ProfitLine or PrimePower values (i.e. blank). Somehow, during testing, we tested on races where those values were ZERO (= 0) but not blank 🤔.


Use a copy of your Model file (i.e. make a backup).


On the 2 retrieval sheets, in cell A13 (where the formula is stored), delete the existing formula and replace with the formula below.

If not comfortable doing that, I will issue an updated Model Sheet with the final public update next week which people can use. I haven't figured out the best advice/method yet to migrate existing Model sheets and formulas to an updated one (like this formula update/migration). I'll work on it.

--------------------------------------------------------------------------------
=IFERROR(UNIQUE(SORT(FILTER(datarange,
(finish>=$B$5) *
(IF($C$5="",finish=$B$5,finish<=$C$5)) *
(IF($B$7="",1,ISNUMBER(MATCH(surface,TRIM(TEXTSPLIT($B$7,",")),0)))) *
(distance>=$B$6) *
(IF($C$6="",distance=$B$6,distance<=$C$6)) *
(IF($B$8="",1,ISNUMBER(MATCH(track,TRIM(TEXTSPLIT($B$8,",")),0)))) *
(IFERROR(SWITCH($B$9,"W",LEFT(racetype,1)<>"M","M",SEARCH("M? ",racetype),"G",SEARCH("G? ",racetype),SEARCH($B$9,racetype)),0)) *
(total_energy>0) *
(adj_bl_final<99) *
(contender_group<99) *
((ISBLANK(primepower)) + ((primepower>0)*(primepower<=99))) *
((ISBLANK(profitline)) + (profitline<=99)) *
IF(B10<>"", IF(C10<>"", IF(B10=C10,date=B10, (date>=B10)*(date<=C10)), date>=B10), IF(C10<>"",date<=C10,1)),
"No records found!"), {1,2,3}, {-1,1,1})), "No data available!")
--------------------------------------------------------------------------------

Do this for both the Filter Example and Leverage/Pace sheets - cell A13. Then your missing AQU race date should populate in the models because we now test whether the PL or BPP values are actually blank (i.e. missing).

I hope that helps!

Ted

[gandalf380]

thanks Ted. Seems to work perfectly.

[Ted Craven]

That's great Charlie! I updated the Excel Model File Blank Template (downloadable) and also the format of the Excel Model file installed.

The new Version is V1.3. This updated formula is included, as well as a few other things we discussed:

See Point #4 of the May 3 final Release Notes:
http://paceandcap.com/forums/showpos...5&postcount=10

Ted