Basic new stat question

[cag8f]

I'm trying to have Poker Tracker keep track of the rakeback I earn (27%). I read and understood the thread to do that for all tournaments I play. But not all of my tournaments have earned rakeback -- my rakeback just started recently. I think I know how to accomplish what I want, but I'm running into a preliminary problem.

The total rake paid (amt_rake; a stat in the Holdem Tournament Tournaments section) consists of a SUM over the individual tourney rake (amt_rake; a stat in the Holdem Tournament Tournament section). For testing purposes only, I created a new stat in the Tournament section, and named it amt_rake2. The expression merely consisted of (amt_rake + 0) and passed the SQL validity test. However when I created a new summation stat in the Tournaments section, with the expression sum(amt_rake2), it did NOT pass the SQL validity test. Why does this fail, while the exact same summation with the built in amt_rake stat work? Any help is appreciated.

[WhiteRider]

You can't use other columns in a column expression - you need to refer to the database tables directly.
If you only started getting rakeback after a certain date you could add a date check into the column expression.
What is the expression for the column in the original rakeback stat?

[cag8f]

Sorry, for clarity my previous post wasn't entirely accurate. My expressions do indeed contain the full database names, i.e. tourney_holdem_summary_amt_rake and tourney_holdem_summary.amt_rake2. Is this to what you're referring when you say, "you need to refer to the database tables directly."?

The rakeback stat I created in the Tournament section contained the simple expression: tourney_holdem_summary.amt_rake + 0. But this was only for testing purposes, to try to sum over these individual tournament values. Had I gotten that to work, I was going to sum over the expression: if(tourney_holdem_summary.id_tourney<X, 0, tourney_holdem_summary.amt_rake). I was hoping that for each tournament, this stat would return 0 if the tourney ID was before X, and return the rake paid if the tourney ID was after X.

Regardless, a date check (any tourney before 7/6/2010 does not receive rakeback) would do the trick--I didn't know one existed.

[WhiteRider]

tourney_holdem_summary.amt_rake2 does not exist - did you add that to your database manually?

sum( if[tourney_holdem_summary.id_tourney<X, 0, tourney_holdem_summary.amt_rake] ) should work as a column expression.

Did you get it working now anyway?

[cag8f]

Yes, tourney_holdem_summary.amt_rake2 is something I added myself.

I entered your column expression, and it passes the SQL validity test. But when I display the corresponding statistic I created, all values are 0.

[WhiteRider]

What value did you use for X?
That DB field refers to PT3's tournament IDs, not the site's tournament number.

You might be better with a date check, like this:

sum( if[ tourney_holdem_summary.date_start < (timestamp '2010-01-01 00:00'), 0, tourney_holdem_summary.amt_rake ] )

[cag8f]

I used the Full Tilt tourney ID for X. I changed the value to tourney_holdem_summary.tourney_no, and that appears to be working now. Thanks for all your help.

I initially wanted to use the timestamp method, but couldn't really find any documentation on the timestamp format I needed to use. Where might it be? I looked in the custom reports and custom statistics guides. Also, the expression you gave me, sum( if[ tourney_holdem_summary.date_start < (timestamp '2010-01-01 00:00'), 0, tourney_holdem_summary.amt_rake ] ), did not pass the SQL validity test.

[WhiteRider]

What section are you building your stat in? That expression validated for me in Holdem Tournament Tournaments.

I got the timestamp documentation from the PostgreSQL website.

[cag8f]

OK it validated for me now--don't know what I was doing wrong earlier. Thanks for the SQL website.

[WhiteRider]

You're welcome.