I have a dataset about transactions, each event is a transaction detail about response code(success or not), their amount, channel in which they pay through, etc. I want to make a report about each individual with the information like name, the total transactions, amount, channel. Each individual have an ID to identify them but I don't want it in the table.
When I use code like this
|eval SUCCESS = if(RESPONSE_CODE="0",1,0)
|stats count as Total, SUM(SUCCESS)AS Total_suc, SUM(AMMOUNT)AS Total_am BY ID, CHANNEL
The last line of code would be a table listing out individual with total transaction above 20 in each channel, with details as listing above, I want it to look like this:
Robert |20 |2000 |MOBAGE
William|34 |1200 |RT
Harry |23 |3000 |RT
Harry |40 |4000 |VT
An individual might make transactions through many channel and their total transactions might be above 20, but they won't appear on the result, another individual can appear many times because they make >20 transactions through each channel. Further more, the field NAME is customer's input, the only thing indetify them is their ID. Exp: Sarah might make 12 transaction in RT, 15 transaction at VT, their total transactions would be above 20, but still won't appear because this is listing /Channel. Harry, as above, appears two times because they make >20 transaction in both RT and VT.
I have several question I would like to ask: 1. Because I use BY to identify individuals (and channel in which they pay through), I don't know if it correct or not, because the amount of data is too much. 2. Since (stats) can only took out the data that appears behind it, I can't show other details related to that individual, if I adding more data behind BY, I can get them but I afraid adding more constrain could make the result incorrect.
Can someone help me with these problems?
Thank you in advance and sorry if the post is too long.