Invalid expressions in custom stat creation

[Opperwezen]

Hi there,

I exclusively play the 3-handed jackpot format games (spins). Because there's a ton of limping OTB from recreational players (and overlimping from the SB as a consequence) in these games, the BB will ISO very wide to exploit this. I take it this will pollute the standard PT4 BB ISO stat, which I really only intend to track BB ISO's vs SB limps (after BU folds).

To be certain this doesn't occure, and because I want to differentiate between AI ISO's and NAI ISO's at a couple different stack ranges, I've tried to make some custom BB ISO stats. Unfortunately, I can't seem to get these to work.

I've tried to configure the following in a seperate group:
- Name + tracked hands (that involve a BU fold and a SB limp at 10-15bb, giving BB the option to ISO)
- Stat 1: 12-15bb (effective stack) NAI ISO BB vs SB
- Stat 2: 12-15bb (effective stack) AI ISO BB vs SB
- Stat 3: 10-11bb (effective stack) NAI ISO BB vs SB
- Stat 4: 10-11bb (effective stack) AI ISO BB vs SB

To ensure that the stat tracks only hands where the effective stack/shortest stack involved in the hand is between these stack sizes, and to ensure that the stat only tracks hands where the BU has folded, I've created the following opportunity and action columns to help create these custom stats:

Opportunity column expression for 12-15bb (effective stack) NAI ISO BB vs SB:

sum(if[tourney_hand_player_statistics.position = 8
and tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb BETWEEN 12.0 AND 15.0
and tourney_hand_player_statistics.flg_p_open_opp = false
and lookup_actions_p.action LIKE 'L%'
and tourney_hand_player_statistics.id_hand in (
select thps_btn.id_hand from tourney_hand_player_statistics thps_btn
where thps_btn.position = 0 and thps_btn.flg_p_fold
), 1, 0])

This opportunity column is deemed valid by PT4, but I'm still uncertain if I did this right.

Action column expression for 12-15bb (effective stack) NAI ISO BB vs SB:

sum(if[tourney_hand_player_statistics.position = 8
and tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb BETWEEN 12.0 AND 15.0
and tourney_hand_player_statistics.flg_p_face_limper
and tourney_hand_player_statistics.flg_p_raise
and not tourney_hand_player_statistics.flg_p_allin
and tourney_hand_player_statistics.id_hand in (
select thps_btn.id_hand from tourney_hand_player_statistics thps_btn
where thps_btn.position = 0 and thps_btn.flg_p_fold
), 1, 0])

This action column is deemed invalid by PT4. I'm not sure what I did wrong and where to go from here. Could you lend me a hand and point out what's going wrong?

Once I've successfully created these custom stats, I want to exclude involvement from the BU in a number of other stats as well. For example, I don't want my opponent's SB (NAI and AI) opening range stats to be polluted by BU limps that aren't a part of conventional strategy. I just want to know how my opponents play the more conventional SB vs BB strategy when the BU has folded. Could you let me know if these custom stats can successfully be created as well?

Kind regards,
Tim

[Flag_Hippo]

There are a few problems with the columns. The action column is invalid because flg_p_face_limper, flg_p_raise and flg_p_allin don't exist in the database schema. The opportunity column validates but will never count any hands, because lookup_actions_p.action LIKE 'L%' checks the BB's own action, and the BB never limps (a limp is also recorded as 'C', not 'L'). Finally, your stack ranges leave a gap, as an effective stack between 11bb and 12bb (for example 11.5bb) isn't counted in either the 10-11bb or the 12-15bb statistics.

The subquery also isn't needed which could affect performance, as columns with subqueries can't be cached. Instead you can use the preflop actors and aggressors strings. In a 3-handed game the BU is position 0, the SB is 9 and the BB is 8. If the first player to voluntarily put money in was the SB (str_actors_p starts with 9), the BU must have folded. The aggressors string always starts with 8 (the BB posting), so if the second aggressor is also 8 the BB raised over the SB limp. See this post for more information on how the actors and aggressors strings work.

Opportunity, 12-15bb:

Code: Select all

sum(if[tourney_hand_player_statistics.position = 8
and round(tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb) between 12 and 15
and tourney_hand_summary.str_actors_p LIKE '9%'
and (tourney_hand_summary.str_aggressors_p = '8' or substring(tourney_hand_summary.str_aggressors_p from 2 for 1) = '8'), 1, 0])

Non all-in ISO, 12-15bb:

Code: Select all

sum(if[tourney_hand_player_statistics.position = 8
and round(tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb) between 12 and 15
and tourney_hand_summary.str_actors_p LIKE '9%'
and substring(tourney_hand_summary.str_aggressors_p from 2 for 1) = '8'
and not (lookup_actions_p.action = 'R' and (tourney_hand_player_statistics.enum_allin = 'P' or tourney_hand_player_statistics.enum_allin = 'p')), 1, 0])

All-in ISO, 12-15bb:

Code: Select all

sum(if[tourney_hand_player_statistics.position = 8
and round(tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb) between 12 and 15
and tourney_hand_summary.str_actors_p LIKE '9%'
and substring(tourney_hand_summary.str_aggressors_p from 2 for 1) = '8'
and lookup_actions_p.action = 'R' and (tourney_hand_player_statistics.enum_allin = 'P' or tourney_hand_player_statistics.enum_allin = 'p'), 1, 0])

The effective stack is rounded here so that there are no gaps between your ranges, which means 11.5bb counts as 12bb and 11.4bb counts as 11bb.

For your SB opening statistics, an open raise opportunity (flg_p_open_opp) already means that nobody has entered the pot before that player, so for the SB in a 3-handed game the BU has always folded. Hands where the BU limped are not included in SB open raise statistics.

[Opperwezen]

Thanks for your time, Hippo.

I've tried to create the columns with the codes you provided, but am unable to validate them (see screenshots). The only code that's valid according to PT4 is the one for the opportunity column.

Any idea how we can fix up the code so it goes through? I've tried fixing it by letting AI work through the issue, but the results suggest that's a bad idea.

Thanks again,
Tim

[Flag_Hippo]

Ah thanks. The two ISO columns contained a list with a comma in it which doesn’t work inside a column. I've edited my original reply and the updated expressions now avoid the comma and validate.

[Opperwezen]

Ah thanks. The two ISO columns contained a list with a comma in it which doesn’t work inside a column. I've edited my original reply and the updated expressions now avoid the comma and validate.

That did the business, thanks! All well and good now.

I requested a paid AI model to do the same for a relatively simple stat to experiment a bit: SB limps in the same stack ranges (after BTN folds). I gave the AI these expressions for ISO opportunities and actions as examples of what to do. It's output was immediately validated by PT4 on the first try. Are these columns and the value expression correct?

SB limp — 8–9bb

Opportunity: cnt_sb_limp_opp_8_9bb

sum(if[tourney_hand_player_statistics.position = 9
and tourney_hand_player_statistics.flg_p_open_opp
and round(tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb) between 8 and 9, 1, 0])

Action: cnt_sb_limp_act_8_9bb

sum(if[tourney_hand_player_statistics.position = 9
and tourney_hand_player_statistics.flg_p_open_opp
and tourney_hand_player_statistics.flg_p_limp
and round(tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb) between 8 and 9, 1, 0])

Value expression:

(cnt_sb_limp_act_8_9bb / cnt_sb_limp_opp_8_9bb) * 100

What is your take on using AI to make PT4 more easily accessible? For you simple stats like this are obviously light work by now, but it takes quite a lot of time for consumers that are less familiar with coding. Have you personally experimented with AI to save time on this, or would you recommend against the use of it?

Thanks for your help, Hippo.

Tim

[Flag_Hippo]

Are these columns and the value expression correct?

Yes, they are correct.

What is your take on using AI to make PT4 more easily accessible? For you simple stats like this are obviously light work by now, but it takes quite a lot of time for consumers that are less familiar with coding. Have you personally experimented with AI to save time on this, or would you recommend against the use of it?

You can certainly use AI and with good context it can help a lot. Providing the PokerTracker 4 database schema is a good start, as it helps avoid pointing to things that don't exist in the schema, like the examples in your earlier expressions. You’ll find the PokerTracker 4 database schema here:

C:\Program Files (x86)\PokerTracker 4\Data\Schemas\schema.postgres.sql

Keep in mind that an expression validating in PokerTracker 4 only means the syntax is correct, not that it counts the right hands. To test a column, create a hand report, click the ‘Filters’ link and select ‘Add New Expression Filters’. Enter the expression with the sum(if[ and , 1, 0]) parts removed. For example:

Code: Select all

tourney_hand_player_statistics.position = 9 and tourney_hand_player_statistics.flg_p_open_opp and tourney_hand_player_statistics.flg_p_limp and round(tourney_hand_player_statistics.amt_p_effective_stack / tourney_blinds.amt_bb) between 8 and 9

Then check that the hands listed are the ones you expect. If you’d like anything double-checked, just let us know.