sum of a field depending on values of other field

[surekhasplunk]

Hi,

I have few fields in my csv file like below. Name of csv file example.csv
A B C D
1 Harry 100 Tel
Tom Harry 200 Ban
1 Harry 300 Oper

Requirement : I want to search in inputlookup example.csv to get
If column A=1 and Column B=Harry then give me sum(Column C) i.e. 400 and give it name as "Perm"
also to the same query add one more criteria like
If Column A=Tom and Column B=Harry then sum(Column C) shold be the output and named as "Cont" and group them by Column D at the end.

[gcusello · Accepted solution]

Hi surekhasplunk,
try something like this:

your_search 
| eval Perm=if(A=1 AND B="Harry",C,0), Cont=if(A="Tom" AND B="Harry",C,0)
| stats sum(Perm) AS Perm sum(Cont) As Cont BY D

Bye.
Giuseppe

[surekhasplunk]

Hi,

Thanks for the answer but unfortunately am getting 0 for Perm and Cont fields. My field names have spaces in it so am double quoting them but still am getting 0s . Not sure why

Thanks

[surekhasplunk]

Thanks,

I tried it unfortunately am getting the results populated with values 0 for Perm and Cont columns

[gcusello]

Hi surekhasplunk,
your requirement was to have two distinct columns, if you want, you can put them in the same column using coalesce:

your_search 
| eval Perm=if(A=1 AND B="Harry",C,0), Cont=if(A="Tom" AND B="Harry",C,0)
| stats sum(Perm) AS Perm sum(Cont) As Cont BY D
| eval column=coalesce(Perm,Cont)
| table D column

You could also display kind of sum (Perm or Cont) in the same column:

your_search 
| eval Perm=if(A=1 AND B="Harry",C,0), Cont=if(A="Tom" AND B="Harry",C,0)
| stats sum(Perm) AS Perm sum(Cont) As Cont BY D
| eval Perm="Perm="+Perm, Cont="Cont="+Cont, column=coalesce(Perm,Cont)
| table D column

Bye.
Giuseppe

[surekhasplunk]

Thanks,

It worked I found for the column names where we have got space we have to single quote them and values where we have space we have to double quote them .