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
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
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
and also Error in 'eval' command: The 'in' function is unsupported or undefined.
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")
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.
my search
|eval group1= if(("Category Group"="A") OR ("Category Group"="B") OR ("Category Group"="C")
AND("Category Group"!= "D"),0,Price)
|appendpipe [stats sum(group1) as Price|fillnull Category value=E1 |fillnull "Category Group" value=E]
|autoregress Price|appendpipe [eval Price=if(Category="E1",Price-Price_p1,null())
|replace E* with F* in Category, "Category Group" |where isnotnull(Price)]
|fields - group1 Price_p1
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)
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
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 ).
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