topic Re: Arithmatic operations based on selected rows in Splunk Search

Arithmatic operations based on selected rows

deepa_purushoth — Tue, 21 Nov 2017 17:33:45 GMT

Hi,
I am a beginer
My data something like this
CategoryGroup | Category |Price
A|A1|1
B|B1|2
B|B2|3
C|C1|1
C|C2|2
C|C3|4
D|D1|3
D|D2|2

I want to add selective rows based on price and display it as new row, further i want to find difference/avg/% and to be displayed each as new rows (col is also ok)
for example
A|A1|1
B|B1|2
B|B2|3
C|C1|1
C|C2|2
C|C3|4
D|D1|3
D|D2|2
E|E1|9 (ie., sum of A,B and C based on selected CategoryGroup
F|f1|7 (ie., difference of E and D , base don selected category group.

Thanks

Re: Arithmatic operations based on selected rows

kamlesh_vaghela — Tue, 21 Nov 2017 17:58:53 GMT

Hi

Can you please try this search?

YOUR_SEARCH
| table CategoryGroup, Category,Price
| eval Price1=if(CategoryGroup="D",0,Price) | addcoltotals |fillnull value="E" CategoryGroup |fillnull value="E1" Category | eval Price3=case(CategoryGroup="D",Price * (-1),CategoryGroup="E",Price)  | addcoltotals |fillnull value="F" CategoryGroup |fillnull value="F1" Category | eval Price=case(CategoryGroup="D",Price,CategoryGroup="E" OR CategoryGroup="F",Price3,1=1,Price) | table CategoryGroup Category Price

Thanks

Re: Arithmatic operations based on selected rows

cmerriman — Tue, 21 Nov 2017 19:06:33 GMT

do you mean based on selected Category and CategoryGroup?

It looks like, in your example, that E|E1|9 would be the sum of A1, B1, B2, C1, C2, but not C3. and f1 would be the difference of E1 and D2.

you could do something like:

|makeresults |eval data="CategoryGroup=A,Category=A1,Price=1 CategoryGroup=B,Category=B1,Price=2 CategoryGroup=B,Category=B2,Price=3 CategoryGroup=C,Category=C1,Price=1 CategoryGroup=C,Category=C2,Price=2 CategoryGroup=C,Category=C3,Price=4 CategoryGroup=D,Category=D1,Price=3 CategoryGroup=D,Category=D2,Price=2"|makemv data|mvexpand data|rename data as _raw|kv|table CategoryGroup Category Price|eval group1=if((like(Category,"%1") OR like(Category,"%2")) AND in(CategoryGroup,"A","B","C"),Price,0)|appendpipe [stats sum(group1) as Price|fillnull Category value=E1 |fillnull CategoryGroup value=E]|autoregress Price|appendpipe [eval Price=if(Category="E1",Price-Price_p1,null())|replace E* with F* in Category, CategoryGroup |where isnotnull(Price)] |fields - group1 Price_p1

Re: Arithmatic operations based on selected rows

deepa_purushoth — Wed, 22 Nov 2017 09:24:25 GMT

Hi cmerriman,
Thanks, i tried running this query but am getting error ": This command must be the first command of a search" and also i would like to add the price column is not indexed field it is derived field so i excluded that field while running. Further I restricted my search only to field "Category group" as the Category and Price field value is not static.
Can you please help me here? Thanks in advance

Re: Arithmatic operations based on selected rows

deepa_purushoth — Wed, 22 Nov 2017 09:50:56 GMT

and also Error in 'eval' command: The 'in' function is unsupported or undefined.

Re: Arithmatic operations based on selected rows

deepa_purushoth — Wed, 22 Nov 2017 12:07:21 GMT

Hi kamlesh_vaghela.

Thanks, however can you please explain me the query flow, because the total adds up is not correct, it is fetching first and the last row.
I treid adding more than one condition using OR in line 3, but it didnt work...⚠ Error in 'eval' command: The expression is malformed. Expected ).

Re: Arithmatic operations based on selected rows

cmerriman — Wed, 22 Nov 2017 12:50:02 GMT

Sorry, what I sent was just as an example, a run anywhere set.
Try to use the query from eval group1 and down.

The in function is also only available after v6.6.
It can be replaced with (CategoryGroup="A" OR CategoryGroup="B" OR CategoryGroup="C")

Re: Arithmatic operations based on selected rows

kamlesh_vaghela — Wed, 22 Nov 2017 17:22:42 GMT

Hi@deepa_purushothaman,

I'm using this search.

| makeresults 
| eval CategoryGroup="A", Category="A1",Price="1" 
| append 
    [| makeresults 
    | eval CategoryGroup="B", Category="B1",Price="2" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="B", Category="B2",Price="3" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="C", Category="C1",Price="1" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="C", Category="C2",Price="2" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="C", Category="C3",Price="3" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="D", Category="D1",Price="3" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="D", Category="D2",Price="2" ] 
| table CategoryGroup, Category,Price 
| eval Price1=if(CategoryGroup="D",0,Price) 
| addcoltotals 
| fillnull value="E" CategoryGroup 
| fillnull value="E1" Category 
| eval Price3=case(CategoryGroup="D",Price * (-1),CategoryGroup="E",Price) 
| addcoltotals 
| fillnull value="F" CategoryGroup 
| fillnull value="F1" Category 
| eval Price=case(CategoryGroup="D",Price,CategoryGroup="E" OR CategoryGroup="F",Price3,1=1,Price) 
| table CategoryGroup Category Price

In this search, with category A, B, C & D,
create a new category "E" which has total value of A + B + C.

| eval Price1=if(CategoryGroup="D",0,Price) 
| addcoltotals 
| fillnull value="E" CategoryGroup 
| fillnull value="E1" Category

Then creating "F" category which has E - D values.

| eval Price3=case(CategoryGroup="D",Price * (-1),CategoryGroup="E",Price) 
| addcoltotals 
| fillnull value="F" CategoryGroup 
| fillnull value="F1" Category

Then again assigning Price fields to a particular category.

| eval Price=case(CategoryGroup="D",Price,CategoryGroup="E" OR CategoryGroup="F",Price3,1=1,Price)

can you please share your sample search in which you get the error?

Thanks

Re: Arithmatic operations based on selected rows

deepa_purushoth — Tue, 29 Sep 2020 16:56:16 GMT

Hi cmerriman,

Thanks for the input, based on that i updated my condition and tried, however in total sum i am not getting expected output. ie.,i want to sum category group A, B and C but not D. but my total sum includes D as well ie., A+B+C+D as E and F as E-D which is not correct, please advice.

i simplified my search by considering only category group as category may have more/less items depends on the condition.

Re: Arithmatic operations based on selected rows

cmerriman — Mon, 27 Nov 2017 12:52:29 GMT

it looks like your search is actually ONLY summing D as E and then subtracting the last value of D (in your original example, it would be a 2), from E as F.

try this:

|eval group1= if(('Category Group'="A") OR ('Category Group'="B") OR ('Category Group'="C") AND ('Category Group'!= "D"),Price,0)