Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two resident tables with the following information:
T1:
Load inline
[PL_PLAYER_ID, PL_TIME_HOUR, GM_NR_BETS,
'A', 20171009, 10,
'B', 20171009, 20,
'A', 20171020, 25,
'A', 20171021, 10];
T2;
Load inline
[PL_PLAYER_ID, PL_TIME_HOUR, PL_OPER_TYPE, PL_VALUE,
'A', 20171005,'C',1000,
'B', 20171008,'C',500,
'A',20171012,'D',100,
'A',20171018,'D',150,
'B',20171019,'D',10]
I'm tring to build a measure which will give the sum of GM_NR_BETS after MAX(PL_TIME_HOUR) when PL_OPER_TYPE='D' for a specific PL_PLAYER_ID.
I'm using the following, but it always return me 0
SUM(IF(TP_TIME_HOUR > aggr(MAX({$<PL_OPER_TYPE={'DEBITO'}>}TP_TIME_HOUR),PL_PLAYER_ID), GM_NR_BETS, 0))
i was expecting the following:
For PL_PLAYER_ID='A' ==> [sum of GM_NR_BETS after MAX(PL_TIME_HOUR) when PL_OPER_TYPE='D'] = 35
For PL_PLAYER_ID='B' ==> [sum of GM_NR_BETS after MAX(PL_TIME_HOUR) when PL_OPER_TYPE='D'] = 0
Can anyone help with this Measure building, please ?
Thanks in advance.
Francisco
Hola Francisco,
Please help me understand your requirement: PL_TIME_HOUR appears on both tables so they are associated and you want the bets after the max of PL_TIME_HOUR? As the field is the same there will be nothing after the max and everything before the max
Can you please confirm?
Cheers,
Luis
Hi,
I assume PL_TIME_HOUR fields are actually different on both table. Otherwise there is no match between two table. I guess the date field on T2 is a referance date.
T1:
Load inline
[PL_PLAYER_ID, PL_TIME_HOUR, GM_NR_BETS,
'A', 20171009, 10,
'B', 20171009, 20,
'A', 20171020, 25,
'A', 20171021, 10];
T2;
Load inline
[PL_PLAYER_ID, PL_TIME_HOUR_REF, PL_OPER_TYPE, PL_VALUE,
'A', 20171005,'C',1000,
'B', 20171008,'C',500,
'A',20171012,'D',100,
'A',20171018,'D',150,
'B',20171019,'D',10]
Measure : SUM(AGGR(SUM({<PL_TIME_HOUR={">$(=MAX(PL_TIME_HOUR_REF))"},PL_OPER_TYPE={'D'}>}GM_NR_BETS),PL_PLAYER_ID))
If not showing PL_PLAYER_ID when Total GM_NR_BET is zero not a problem then you can remove the aggr part of the measure
SUM({<PL_TIME_HOUR={">$(=MAX(PL_TIME_HOUR_REF))"},PL_OPER_TYPE={'D'}>}GM_NR_BETS)
Hi Luis,
In the beggining i had different variables for the PL_TIME_HOUR and those represent when the events in table T1 and table T2 occured in time.
If i have 2 variables about time, how can i correlate both in a unique time variable ?
Thanks in advance for the support,
Cheers,
Francisco
P.S.: Sorry for the late reply, though help much appreciated.
Hi ,
If i have 2 representing the time when the events occurred , how can i correlate both in a unique time variable ?
Thanks in advance for the support,
Cheers,
Francisco
P.S.: Sorry for the late reply, though help much appreciated.
Hi,
Can you please explain the difference between the two dates?
in the meantime, please review this post as it could be helpful for this design Canonical Date
Cheers,
Luis
Thanks that post helped a lot and solved my problem