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.
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
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
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
Thanks,
I tried it unfortunately am getting the results populated with values 0 for Perm and Cont columns
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
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 .