How can i use stats in below mentioned Query?

[shankaranantht]

Find below mentioned data

Applicationname |Partners | Servicename | status
DEE | WEEEEE |Money Transfer|Success
DDD |CDDDDD | Money Transfer | Success
DDD |FFFFFFF|Money Transfer |Failed
AAA |WEEEEE |In Payee hand |Success
DEE |WEEEEE |In Payee hand |Success
DDD|CDDDDD |In Payee hand |Success
AAA |WEEEEE|Money Transfer |Success
AAA |WEEEEE |Money Validate |Success
AAA |WEEEEE |Money Receive |Success
AAA |WEEEEE |Cross Verify |Success

I have framed the query like

Sample Query

Source =data.csv
| eval status=if(isnotnull(Error), "Failed", "Success")
| eval Failcount=case(Status="Failed",1) | eval Successcount=case(Status="Success",1)
| stats count(Status) as Total_Transactions count(Failcount) as Failed_Transactions count(Successcount) as Success_Transactions values(Servicename) as Servicename
values(Applicationname) as Applicationname by PartnerId
| table partnerids ApplicationName ServiceName Success_Transactions Failed_Transactions Total_Transactions

I need the result as mentioned in the attachment, can someone help me please

Thanks in advance

[sundareshr · Accepted solution]

This should give you what you are looking for

source=data.csv 
| chart values(Partners) as Partners  list(Servicename) as Service list(eval(if(status="Success", 1, 0))) as Success list(eval(if(status="Failed", 1, 0))) as Fail count(status) as Txns over Applicationname

[shankaranantht]

Thank u very much sundaresh.

There was a small changes need to be done in the query, but i was unable to do it.
I cannot able to get the expected result.

I need the result as mentioned below .

Applicationname partner ServiceName Succescount failcount Total
AAA WEEEEE In Payee hand 12 0 67
Money Transfer 12 0
Money Validate 19 0
Money Receive 12 12

I'm using the below query.

source=data.csv
| eval status=if(isnotnull(Error), "Failed", "Success")
|eval partnerids = coalesce(partnerid,PartnerId)
| chart values(ApplicationName) as ApplicationName values(ServiceName) as ServiceName list(eval(if(status="Success", 1, 0))) as Succalt textess
values(eval(if(status="Failed", 1, 0))) as Fail count(status) as Txns over partnerids

Thanks in advance.

[sundareshr]

If your data is as you have specified in the question, your query will not work. The sample data does not have a "Error", partnerid or PartnerId fields. Your eval statements will fail. Now, assuming your actual data does have those fields... what result are you getting when you run your query?

[shankaranantht]

I Re-framed the query which u sent and got the result sundareshr .
Thank you very much.

[jplumsdaine22]

shankarananth - I have verified that sundaresh query is is correct based on the sample data you provided. IE if you import that sample data set you provided as a csv, and run the exact query provided by sundaresh you will get the results you are asking for .

EG: ![alt text][http://imgur.com/uQEdwAK]

If its not working for you check that the spelling (including capitals) of field names are correct - a spelling error will give you empty fields

[shankaranantht]

Thank u very much sundaresh.

There was a small changes need to be done in the query, but i was unable to do it.
I cannot able to get the expected result.
Note: kindly find the attachment for my expected result.

I'm using the below query.

source=data.csv
| eval status=if(isnotnull(Error), "Failed", "Success")
|eval partnerids = coalesce(partnerid,PartnerId)
| chart values(ApplicationName) as ApplicationName values(ServiceName) as ServiceName list(eval(if(status="Success", 1, 0))) as Succalt textess
values(eval(if(status="Failed", 1, 0))) as Fail count(status) as Txns over partnerids