Referencing one column from another, within player statistic

[shalder]

Would like to know if this is possible...

I created a query that determines if the board is paired (3 kind, etc..).
I created another query that determines hand strength when player raises/checkraises based on data in the board texture column, e.g. if bored is paired than 1 pair doesn't mean much.

If i put both functions in "Holdem Cash Hands" they both work flawless.
If i want to use them as a player statistic though on my hud, i have to put them in cash player statistics, right?

So i move both functions over to cash player statistics.
I wrap the hand strength query in a sum() expression, because it is actually summing all the different strengths. (i have a similar function to act as a denominator).

Now the problem is this, I cannot get the hand strength query to retrieve any info from the board texture column. It can only access the innate programmed data base fields.
Not other columns that give info on specific hands. When both statistics were in "Holdem Cash Hands" one column referencing another column worked fine - probably because they were
both data points on in individual cash hands. But now that one sums data over alll cash hands, and within its sum expression it references data on individual cash hands, it no longer works.
Only the innate database fields are accessible from within the sum expression, not other columns?

[just some extra info, when i reference one column from another, it inputs as VALID SQL, the error occurs when it actually tries to retrieve the data. I get a ton of red errors on the report screens whenever i try to display the statistic/column. Again, accessing one column from another within cash hands is working fine.]

Anyway around this?

[kraada]

You should be able to get data from the holdem_hand_player_detail table while create a stat in holdem_hand_player_statistics (aka hhps). You just need to reference the whole table.

For example,
sum(holdem_hand_player_detail.val_f_bet_made_pct)
works fine when creating a column in hhps.

What's the column you're trying to create? I'd be happy to help you get it working.

[shalder]

Ok here is my code to check if the board is paired....
I create this statistic in HHPS...and call it texture_paired_f

It returns 0 if the boared is xyz, 1 if boared is xxy, and 2 if boared is xxx


if[holdem_hand_summary.card_1 = holdem_hand_summary.card_2 + 13, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_2 + 26, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_2 + 39, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_2 - 13, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_2 - 26, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_2 - 39, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_3 + 13, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_3 + 26, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_3 + 39, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_3 - 13, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_3 - 26, 1, 0] +
if[holdem_hand_summary.card_1 = holdem_hand_summary.card_3 - 39, 1, 0] +
if[holdem_hand_summary.card_2 = holdem_hand_summary.card_3 + 13, 1, 0] +
if[holdem_hand_summary.card_2 = holdem_hand_summary.card_3 + 26, 1, 0] +
if[holdem_hand_summary.card_2 = holdem_hand_summary.card_3 + 39, 1, 0] +
if[holdem_hand_summary.card_2 = holdem_hand_summary.card_3 - 13, 1, 0] +
if[holdem_hand_summary.card_2 = holdem_hand_summary.card_3 - 26, 1, 0] +
if[holdem_hand_summary.card_2 = holdem_hand_summary.card_3 - 39, 1, 0]


Now the following function i call Hand_strength_f.
It returns a number identifying the type of hand.

But this is the important feature of this function, it returns 0 even if
opponent has a pair (or 3 of a kind) if the boared is paired (xxy) or 3kind (xxx).


sum(if[holdem_hand_player_combinations.flg_f_strflush, 9,
if[holdem_hand_player_combinations.flg_f_fouroak, 8,
if[holdem_hand_player_combinations.flg_f_fullhouse, 7,
if[holdem_hand_player_combinations.flg_f_flush, 6,
if[holdem_hand_player_combinations.flg_f_straight, 5,
if[texture_paired_f != 2 AND holdem_hand_player_combinations.flg_f_threeoak, 4,
if[holdem_hand_player_combinations.flg_f_2pair, 3,
if[texture_paired_f = 0 AND holdem_hand_player_combinations.flg_f_1pair, 1, 0]]]]]]]])


Now the error i get is when texured paired is referenced from within the sum() expression.
When i put this same code in single-cash-hands, i didn't use a sum() expression because we
were always just looking at a single hand. But now we want to look at all hands.

[kraada]

Well, the second of your functions is already in the database:
lookup_hand_ranks.id_group is the same function as what you're trying to build, so you should be able just to use that database number and have it work okay.

You can also make your paired flop stat much simpler; the key is the modulo function. Modulo returns the remained when you divide. With the way the database is set up, if card_1 and card_2 are of the same rank, card_1 % 13 = card_2 % 13.

This should make your stat quite a bit simpler to build and it might resolve some issues as well.

Also, sometimes you can't sum() within a column but you can create a second column and take the sum of the first column; I'm not sure of the exact restrictions on that but you could give that a try too.

[shalder]

The only workaround i found is to cut and paste function 1 into function 2, and that works.
(i tried creating 3 columns, like u suggested, using the 3rd column simply to sum, and that didn't work for this :(

So I already got this particular function working (granted its practically unreadable cause i had to copy F1 into F2 into 2 different spots), but i'm still interested if anyone comes up with a work around in general,
because of all the nifty things u can do (e.g. write one function to identify ace flops etc), another to see how much your opponents folds them, etc.

Right now the cut and pasting makes this kind of stuff unwieldy to write, upkeep, revise, etc... I grateful for the functions u identified that may let me shorten some of the functions i wrote, but i still think there's a lot of value to being able to do this generally. Granted these types of functions are near the higher end of what u guys desgined poker tracker to do...but its so close already to being able to do them (it works cut and pasting, and it works when both hands are in cash game single hand (no summing) ),that I would greatly appreciate it if it became fully functional.

[kraada]

Well, I'll let you in on a little secret:

Filters for "Any A on Flop" are coming, along with a whole slew of other flop texture filters for the Filters interface.

I hope they'll be ready for Beta 21 but they might not make it in until Beta 22 . . . which will make a lot of what you're trying to do way, way easier.

[WhiteRider]

The only workaround i found is to cut and paste function 1 into function 2, and that works.
(i tried creating 3 columns, like u suggested, using the 3rd column simply to sum, and that didn't work for this :(

You're right - that's the only way to do it currently.
Each column (in the HCPS section) is based on all the hands played by the player, so if you use a column in the creation of another one then the 'total' column will be used for each element of the SUM. What you need to be able to do is use a column from the Holdem Cash Hand section, but you can't do this. I've mentioned this to the developers in the past as I think it would simplify construction of some stats in the HCPS section.

[shalder]

Yeah i really whish they do that white rider... :)

On a related note, it would also cut down on the need for creating duplicate functions in each section.
I tend to find that i create one function in cash hands, and the exact same function in player statistics,
with the only exception being the on in player statistics is wrapped in a sum(if[true/false, 1,0]) statement.

e.g. take an existing function: 3 bet Preflop, this function is identical in player statistics, except that one is
wrapped in a sum(if[true/false, 1, 0]) statement. Rather player statistics could just grab the code (or result from the table)
and do the computation from there. I think all the functions in player statistics which are dervied from individual cash hands follow this format.

[WhiteRider]

I completely agree, and I'll make sure the developers are aware of this discussion.

[APerfect10]

The problem with that is then the custom stat would require joining tables which is extremely slow...

It's a double-edged sword. Slow queries joining two tables or faster queries on a single table.

While it would be extremely easy to join the tables to provide you with this information, the performance simply is not yet acceptable therefore something major would need to change which is not so easy...

Best regards,

Derek