int in custom stats

[banshee]

hi,

I'm actually trying to develop a statistic that holds the values 0-3 for the card suits c, d, h, s. To accomplish that I subtract 1 of the flopcard identifier and divide by 13 (So the card values 1-13 should get mapped to 0, 14-26 to 1, etc.). Now I have a card suit identifier for each flopcard and would like to filter for things like flop1_suit = flop2_suit AND flop1_suit = flop3_suit (suited boards) but the problem is that the int values get rounded internally unlike to other programming languages where decimal places just get cut.

I used cast((id_flop2 - 1) / 13 as int) as format expression and on a Th 4h 4c board the values are flop1_suit: 3, flop2_suit: 2 but when I filter for flop1_suit != flop2_suit the board doesn't get filtered because internally flop1_suit is 2,61538 and flop2_suit 2,15384 and by casting to int flop1_suit gets rounded to 3 and flop2_suit gets rounded to 2. Do you have an idea how I can avoid that?

[kraada]

I've been looking at a fairly similar problem recently, and I couldn't find a better way than just testing for all of suit combinations. It's a bit tedious, but it does work.

(And built in filters for board texture will be coming, also.)

[banshee]

So you write a filter like this?

board = 2c3c4c or board = 2c3c5c or board = 2c3c6c...

This are 1716 combinations

[kraada]

Not quite that badly; less than and greater than still work fine.

So for a monotone board, say, it's:

((holdem_hand_summary.card_1 <= 13 and holdem_hand_summary.card_2 <= 13 and holdem_hand_summary.card_3 <= 13) or ((holdem_hand_summary.card_1 <= 26 and holdem_hand_summary.card_1 >= 14) and (holdem_hand_summary.card_2 <= 26 and holdem_hand_summary.card_2 >= 14) and(holdem_hand_summary.card_2 <= 26 and holdem_hand_summary.card_2 >= 14) and (holdem_hand_summary.card_3 <= 26 and holdem_hand_summary.card_3 >= 14)) or ((holdem_hand_summary.card_1 <= 39 and holdem_hand_summary.card_1 >= 27) and (holdem_hand_summary.card_2 <= 39 and holdem_hand_summary.card_2 >= 27) and(holdem_hand_summary.card_2 <= 39 and holdem_hand_summary.card_2 >= 27) and (holdem_hand_summary.card_3 <= 39 and holdem_hand_summary.card_3 >= 27) or
(holdem_hand_summary.card_1 >= 40 and holdem_hand_summary.card_2 >= 40 and holdem_hand_summary.card_3 >= 40))) and holdem_hand_player_statistics.flg_f_saw

Similarly you can pick out rainbow boards and flush draw boards this way, you just end up having a somewhat longer string of ands and ors.

[banshee]

And it's also impossible to compare strings characterwise? So you could filter for the 2nd character in "9c", "Ad", etc.

[WhiteRider]

That's a good thought - you could try using SUBSTRING. See the Custom Statistics and Reports FAQ.

[banshee]

I have problems with it though. One the one hand I can use substring in the columns section only and on the other hand substring(lookup_from_id(id_flop1, 'card_rank') from 2 for 1) is not a valid SQL statement

[WhiteRider]

No, I can't get that to work either.
I'll see if I can find out whether this is possible.

[banshee]

I now tried to filter for rainbow boards this way:

(holdem_hand_summary.card_1 < 14 and ((holdem_hand_summary.card_2 > 13 and holdem_hand_summary.card_2 < 27 and ((holdem_hand_summary.card_3 > 26 and holdem_hand_summary.card_3 < 40) or holdem_hand_summary.card_3 > 39)) or (holdem_hand_summary.card_2 > 26 and holdem_hand_summary.card_2 < 40 and ((holdem_hand_summary.card_3 > 13 and holdem_hand_summary.card_3 < 27) or holdem_hand_summary.card_3 > 39)) or (holdem_hand_summary.card_2 > 39 and ((holdem_hand_summary.card_3 > 13 and holdem_hand_summary.card_3 < 27) or (holdem_hand_summary.card_3 > 26 and holdem_hand_summary.card_3 < 40))))) or
(holdem_hand_summary.card_1 > 13 and holdem_hand_summary.card_1 < 27 and ((holdem_hand_summary.card_2 < 14 and (holdem_hand_summary.card_3 < 14 or holdem_hand_summary.card_3 > 39)) or (holdem_hand_summary.card_2 > 26 and holdem_hand_summary.card_2 < 40 and (holdem_hand_summary.card_3 < 14 or holdem_hand_summary.card_3 > 39)) or (holdem_hand_summary.card_2 > 39 and (holdem_hand_summary.card_3 < 14 or (holdem_hand_summary.card_3 > 26 and holdem_hand_summary.card_3 < 40))))) or
(holdem_hand_summary.card_1 > 26 and holdem_hand_summary.card_1 < 40 and ((holdem_hand_summary.card_2 < 14 and ((holdem_hand_summary.card_3 > 13 and holdem_hand_summary.card_3 < 27) or holdem_hand_summary.card_3 > 39)) or (holdem_hand_summary.card_2 > 13 and holdem_hand_summary.card_2 < 27 and (holdem_hand_summary.card_3 < 14 or holdem_hand_summary.card_3 > 39)) or (holdem_hand_summary.card_2 > 39 and (holdem_hand_summary.card_3 < 14 or (holdem_hand_summary.card_3 > 13 and holdem_hand_summary.card_3 < 27))))) or
(holdem_hand_summary.card_1 > 39 and ((holdem_hand_summary.card_2 < 14 and ((holdem_hand_summary.card_3 > 13 and holdem_hand_summary.card_3 < 27) or (holdem_hand_summary.card_3 > 26 and holdem_hand_summary.card_3 < 40))) or (holdem_hand_summary.card_2 > 13 and holdem_hand_summary.card_2 < 27 and (holdem_hand_summary.card_3 < 14 or (holdem_hand_summary.card_3 > 26 and holdem_hand_summary.card_3 < 40))) or (holdem_hand_summary.card_2 > 26 and holdem_hand_summary.card_2 < 40 and (holdem_hand_summary.card_3 < 14 or (holdem_hand_summary.card_3 > 13 and holdem_hand_summary.card_3 < 27)))))

But when I try to save the filter I get an unhandled error exception. The expression gets validated though. It it maybe to long?

[WhiteRider]

I haven't really played much with big columns like this so I'll mark this for Kraada to take another look at.