Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a raw database table like the below, where there are excess number of rows which I want to filter out:
primarykey | market_id | msg_date_time | username | trade_id | completed_amount |
1 | 6 | 10/11/2014 07:50 | aa | 0 | 0 |
2 | 5 | 10/11/2014 07:50 | bb | 0 | 0 |
3 | 7 | 10/11/2014 07:56 | aa | 67 | 10 |
4 | 7 | 10/11/2014 07:56 | aa | 67 | 10 |
5 | 7 | 10/11/2014 08:21 | aa | 67 | 10 |
6 | 7 | 10/11/2014 08:23 | aa | 67 | 10 |
7 | 3 | 10/11/2014 08:36 | ss | 8 | 5 |
8 | 63 | 10/11/2014 08:36 | gg | 0 | 0 |
9 | 3 | 10/11/2014 08:41 | aa | 0 | 0 |
For trade_id=67, I want to sum only the row where msg_date_time is maximum. How can I do this in charts and text boxes?
Sifat,
PFA
I converted your diatomite to timestamp, ordered your table and used new Id to use FirstSortedValue.
The expression is
sum(Aggr(FirstSortedValue (completed_amount,Id),trade_id,username))
Regards,
Sergey
Hi Sifat,
Try to use firstsortedvalue().
Sergey
Thanks Sergey, how exactly would you go about it?
I tried something like this but it doesn't work:
=FirstSortedValue(completed_amount,trade_id)
Sifat,
PFA
I converted your diatomite to timestamp, ordered your table and used new Id to use FirstSortedValue.
The expression is
sum(Aggr(FirstSortedValue (completed_amount,Id),trade_id,username))
Regards,
Sergey
try like
sum(aggr(firstsortedvalue(completed_id,-trade_id),username))
put -trade_id it gives max values