Filter for multiple players in Hand Report

[JackOscar95]

What I've tried so far is first filtering for players in player report and then adding a filter #Player# IN (....) for all those players, but it seems to break when the string becomes too long so that doesn't work. Any better way to do this?

[JackOscar95]

The thing I'm trying to filter for is:

Hands > 350
VPIP > 0.32
PFR/VPIP < 0.65

[Flag_Hippo]

What I've tried so far is first filtering for players in player report and then adding a filter #Player# IN (....) for all those players, but it seems to break when the string becomes too long so that doesn't work. Any better way to do this?

If you want to filter by player names you can use these formats:

Code: Select all

player.player_name = 'name1' OR player.player_name = 'name2'

or

Code: Select all

player.player_name SIMILAR TO '(name1|name2)'

The thing I'm trying to filter for is:

Hands > 350
VPIP > 0.32
PFR/VPIP < 0.65

Filtering a hand report based on player statistics will require a subquery - see this thread for an example.

[JackOscar95]

What I've tried so far is first filtering for players in player report and then adding a filter #Player# IN (....) for all those players, but it seems to break when the string becomes too long so that doesn't work. Any better way to do this?

If you want to filter by player names you can use these formats:

Code: Select all

player.player_name = 'name1' OR player.player_name = 'name2'

or

Code: Select all

player.player_name SIMILAR TO '(name1|name2)'

The thing I'm trying to filter for is:

Hands > 350
VPIP > 0.32
PFR/VPIP < 0.65

Filtering a hand report based on player statistics will require a subquery - see this thread for an example.

Thank you, that gave me enough to go on to create my own subquery to filter with. What I came up with is this:

cash_hand_player_statistics.id_hand in
(select chps.id_hand from cash_hand_player_statistics chps, cash_cache cc where chps.id_player = cc.id_player group by chps.id_hand
having not
(
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.3 and
( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.25 and
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) - ( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.08 and
( (sum(cc.cnt_p_limp) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.02 and
(sum(cc.cnt_hands) > 100) )
or (sum(cc.cnt_hands) > 20000)
)

Unfortunately, my report just gets stuck on "loading..." even after several hours. Do you think this query is too slow to execute or something else? It's a pretty large DB ~25gb.

[Flag_Hippo]

Unfortunately, my report just gets stuck on "loading..." even after several hours. Do you think this query is too slow to execute or something else? It's a pretty large DB ~25gb.

Your filter works for me on a smaller database but on larger databases these types of queries are going to be much slower.

[JackOscar95]

Unfortunately, my report just gets stuck on "loading..." even after several hours. Do you think this query is too slow to execute or something else? It's a pretty large DB ~25gb.

Your filter works for me on a smaller database but on larger databases these types of queries are going to be much slower.

Thanks for trying that out, is there any chance it would finish loading if I waited for like 24+ hours or does it time out well before that?

If so, then maybe I need to find a solution with a "player.player_name SIMILAR TO '(name1|name2)'" approach, the problem there however is that the list of players becomes too large for PT4 to read the query. Is it possible to get around this by creating a table and insert the player names and then write a query with that? e.g.

player.player_name IN (SELECT playername from CustomPlayerTable)

[Flag_Hippo]

Thanks for trying that out, is there any chance it would finish loading if I waited for like 24+ hours or does it time out well before that?

I wouldn't know how long it would take or if it would finish as there are too many variables. If you are going to leave a query running for awhile then I'd suggest testing it on a smaller database first so you can verify it works and you are getting the kind of results you want to see.

If so, then maybe I need to find a solution with a "player.player_name SIMILAR TO '(name1|name2)'" approach, the problem there however is that the list of players becomes too large for PT4 to read the query. Is it possible to get around this by creating a table and insert the player names and then write a query with that?

Unfortunately that's not something I know anything about but bear in mind these filters will give you different kinds of results. A filter using names with give you all hands played by those players in a hand report (with the filter on active player option turned off) from their perspective. The subquery is different as it checks every player in every hand and if any player has matching stats to your query the hand gets returned which is suitable if you wanted to see the hands from your perspective when any of these players are dealt in.

[JackOscar95]

Thanks for trying that out, is there any chance it would finish loading if I waited for like 24+ hours or does it time out well before that?

I wouldn't know how long it would take or if it would finish as there are too many variables. If you are going to leave a query running for awhile then I'd suggest testing it on a smaller database first so you can verify it works and you are getting the kind of results you want to see.

If so, then maybe I need to find a solution with a "player.player_name SIMILAR TO '(name1|name2)'" approach, the problem there however is that the list of players becomes too large for PT4 to read the query. Is it possible to get around this by creating a table and insert the player names and then write a query with that?

Unfortunately that's not something I know anything about but bear in mind these filters will give you different kinds of results. A filter using names with give you all hands played by those players in a hand report (with the filter on active player option turned off) from their perspective. The subquery is different as it checks every player in every hand and if any player has matching stats to your query the hand gets returned which is suitable if you wanted to see the hands from your perspective when any of these players are dealt in.

Oh, I see. What I'm actually interested in seeing the hands from *their* perspective.

From what I can see the player table has the column id_player so I suppose just this should work (also also compiles):

player.player_name in
(select pl.player_name
from player pl, cash_hand_player_statistics chps, cash_cache cc
where pl.id_player = chps.id_player and chps.id_player = cc.id_player
group by pl.player_name
having not
(
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.3 and
( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.25 and
( (sum(cc.cnt_vpip) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) - ( (sum(cc.cnt_pfr) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.08 and
( (sum(cc.cnt_p_limp) * 1.0) / (sum(cc.cnt_hands) * 1.0) - sum(cc.cnt_walks) * 1.0) < 0.02 and
(sum(cc.cnt_hands) > 100) )
or (sum(cc.cnt_hands) > 20000)
)

I guess this should take equally long or even longer to query, however.

[JackOscar95]

Okay, new issue...

I've decided to make do with the "player in" filter, but the problem now is that I can only retrieve 100,001 hands in the report at a time. How do I bypass this? If possible I would only need to be able to export the results to CSV, not view them in the program.

[Flag_Hippo]

I've decided to make do with the "player in" filter, but the problem now is that I can only retrieve 100,001 hands in the report at a time. How do I bypass this? If possible I would only need to be able to export the results to CSV, not view them in the program.

See this post.