Discuss how to create custom stats, reports and HUD profiles and share your creations.
Moderators: WhiteRider, kraada, Flag_Hippo, morny, Moderators
by ommaha » Sun Aug 09, 2015 3:47 pm
Can I filter for hands where I was heads up OTF vs players that share a special stat value? F.e. I want to filter for hands that I played (6 players preflop) heads up out of position OTF against players that have a button-openraise of 70%+.
Is there a way to do this (using expression filters)? Filtering for 6 players preflop, HU oop OTF is not a problem just the part "opponent openraises 70%+ from button".
thank you
-
ommaha
-
- Posts: 218
- Joined: Sun Jul 29, 2012 8:46 am
by kraada » Mon Aug 10, 2015 10:13 am
This can only be done with a filter expression in a custom report.
This ought to do it:
cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps where chps.id_player in (SELECT cc.id_player from cash_cache cc where cc.cnt_players = 6 group by cc.id_player HAVING sum(cc.cnt_p_open_opp_btn) > 0 and ((sum(cc.cnt_p_rfi_btn) * 1.0) / (sum(cc.cnt_p_open_opp_btn) * 1.0) >= .7 ))
-
kraada
- Moderator
-
- Posts: 54431
- Joined: Wed Mar 05, 2008 2:32 am
- Location: NY
by ommaha » Sun Aug 16, 2015 5:20 pm
I just tried it as an expression filter. At first it has been invalid. After I added a third ")" at the end (I hope this is the right place) I have been able to save the expression filter.
cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps where chps.id_player in (SELECT cc.id_player from cash_cache cc where cc.cnt_players = 6 group by cc.id_player HAVING sum(cc.cnt_p_open_opp_btn) > 0 and ((sum(cc.cnt_p_rfi_btn) * 1.0) / (sum(cc.cnt_p_open_opp_btn) * 1.0) >= .7 )))
But it did not show a result. I am just shown error: unable to execute query: fatal error; reason:error: (ERROR: syntax error at or near "ORDER"LINE1: ... INTERVAL ´1 SECOND´+ INTERVAL ´3 HOURS´))) AND ORDER BY (... ^ )
-
ommaha
-
- Posts: 218
- Joined: Sun Jul 29, 2012 8:46 am
by kraada » Mon Aug 17, 2015 8:06 am
Apologies for missing that ) - you are correct it belongs at the end of the expression. This seems to be working for me - are you looking at the cash games side and in a Player report or somewhere else?
-
kraada
- Moderator
-
- Posts: 54431
- Joined: Wed Mar 05, 2008 2:32 am
- Location: NY
by ommaha » Tue Aug 18, 2015 5:36 am
I used it in Hand report and got the error message. I tried it in a player report now (cash games, PLO only) but I might face an endless loop or something similar? It shows "loading" for 40 minutes now but neither postgres nor PT4 seem to be using CPU in the task manager
-
ommaha
-
- Posts: 218
- Joined: Sun Jul 29, 2012 8:46 am
by kraada » Tue Aug 18, 2015 7:50 am
Depending on your database it can take quite a while. 40 minutes does seem a bit excessive but I pasted it into a report and it definitely finished for me. Are you running 4.14?
This won't work on the Hand side because of a known issue with upper case in the filters - this will work on the Hand side:
cash_hand_player_statistics.id_hand in (select chps.id_hand from cash_hand_player_statistics chps where chps.id_player in (select cc.id_player from cash_cache cc where cc.cnt_players = 6 group by cc.id_player having sum(cc.cnt_p_open_opp_btn) > 0 and ((sum(cc.cnt_p_rfi_btn) * 1.0) / (sum(cc.cnt_p_open_opp_btn) * 1.0) >= .7 )))
-
kraada
- Moderator
-
- Posts: 54431
- Joined: Wed Mar 05, 2008 2:32 am
- Location: NY
by ommaha » Tue Aug 18, 2015 8:13 am
Yes, I am running 4.14. It still shows "loading" and I have not interrupted it.
Thanks for the filter on the hand side. I will try it lateron
-
ommaha
-
- Posts: 218
- Joined: Sun Jul 29, 2012 8:46 am
by kraada » Tue Aug 18, 2015 11:31 am
Do you see PostgreSQL using resources in Task Manager?
-
kraada
- Moderator
-
- Posts: 54431
- Joined: Wed Mar 05, 2008 2:32 am
- Location: NY
by ommaha » Tue Aug 25, 2015 1:03 pm
kraada wrote:This won't work on the Hand side because of a known issue with upper case in the filters - this will work on the Hand side:
cash_hand_player_statistics.id_hand in (select chps.id_hand from cash_hand_player_statistics chps where chps.id_player in (select cc.id_player from cash_cache cc where cc.cnt_players = 6 group by cc.id_player having sum(cc.cnt_p_open_opp_btn) > 0 and ((sum(cc.cnt_p_rfi_btn) * 1.0) / (sum(cc.cnt_p_open_opp_btn) * 1.0) >= .7 )))
Hi,
sorry for coming back to this so late. I just tried and I get the same error message in hand report. As I looked closer at the code I realised that the only thing you changed was writing "SELECT" instead of "select", right?
I tried again in an all player report but it is still loading like forever even though I used a fairly small dB (~400k hands). Task manager shows that RAM is used but CPU is not
-
ommaha
-
- Posts: 218
- Joined: Sun Jul 29, 2012 8:46 am
by kraada » Tue Aug 25, 2015 1:42 pm
There's a known issue with uppercase in certain situations which is why I made that particular change. The real think that should be used is disk i/o -- this is a very disk intensive operation.
-
kraada
- Moderator
-
- Posts: 54431
- Joined: Wed Mar 05, 2008 2:32 am
- Location: NY
Return to Custom Stats, Reports and HUD Profiles
Users browsing this forum: Google [Bot] and 13 guests