Date Category Type Count
5 car sedan 300
5 car suv 400
5 car van 400
5 plane com 300
5 plane priv 300
6 booking test 500
6 booking test1 500
6 booking test2 500
I want from above to below
Date Category Type Count
5 car sedan 300
suv 400
van 400
plane com 300
priv 300
6 booking test 500
test1 500
test2 500
And possibly more merging than just 4 columns 🙂
Possibly using table also?
Like this:
| makeresults
| eval raw="5,car,sedan,300
5,car,suv,400
5,car,van,400
5,plane,com,300
5,plane,priv,300
6,booking,test,500
6,booking,test1,500
6,booking,test2,500"
| makemv delim="
" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<Date>[^,]+),(?<Category>[^,]+),(?<Type>[^,]+),(?<Count>.*)$"
| rename COMMENT AS "Everything above creates sample event data; everything above is your solution"
| stats sum(Count) AS Count by Date Category Type
| stats list(Type) list(Count) BY Date Category
I think this is a good use case for autoregress, which will look at the previous row and let you do some comparisons. So, assuming your results are already sorted by date, categories and type...
...
| autoregress Category as prev_Category
| eval Category = if(Category=prev_Category,"",Category)
| fields Date, Category, Type, Count
| stats list(Category) as Category, list(Type) as Type, list(Count) as Count by _time
So if the previous row's values match this rows value, then set this row's value to nothing. And as noted above, once you do this, you really can't interact with the panel any more. The panel will look pretty, but those values are gone and so you won't be able to click on the column headers to sort data while keeping the pretty looking format.
Hope that helps.
EDIT: Just realized what you mean bey wanting this in one rows. I think that can be done with multi-value magic...
EDIT 2: Ok, maybe just need stats list at this point. I updated the search.
Okay, assuming that you are ONLY trying to change the presentation, and never intend to do any more calculation on the data, then you can do this...
(your search with | stats etc that produces)
| table field1 field2 field3 field4 field5...
| rename COMMENT as "Do this streamstats for each field you want to suppress reprints"
| streamstats current=f last(field1) as old_field1, last(field2) as old_field2,last(field3) as old_field3
| fillnull value="((null))" old_*
| rename COMMENT as "A field can only be blank if all higher levels on the same line are blank."
| eval field1=if(field1!=old_field1,field1,"((blank))")
| eval field2=case(field1!="((blank))",field2,field2=old_field2,"((blank))",true(),field2)
| eval field3=case(field2!="((blank))",field3,field3=old_field3,"((blank))",true(),field3)
| rename COMMENT as "Now you can get rid of the ((blank)) flags and all the work (old_*) fields."
| replace "((blank))" WITH "" IN field1 field2 field3
| table field1 field2 field3 field4 field5...
With this, it does stop repeating the values... wish it could merge it into 1 row tho... this is what I have now
5 sedan 300
5 car suv 400
5 van 400
5 plane com 300
5 priv 300
6 test 500
6 booking test1 500
6 test2 500
There are still rows being displayed on the table
Actually, I think I did it wrong, gimme a min
Okay, this really is starting to feel like you are stuck on attempting to commit spreadsheet.
What is your reason for wanting to present the data in this manner? Who is the user, and what specifically will they be using the report for?
If it is just for a display/report, then why does it matter that there are multiple actual lines? They represent multiple detail lines, so they SHOULD BE multiple lines.
In other words, use case, please?
business reviews for shareholders and execs
you can add |eval typeCount=Type+"-"+Count|fields - Type Count|nomv typeCount
or i suppose if the count is always the same, you could just do |nomv Type|nomv Count
if I was to try nomv, it comes after stats?
yup, just tack it on to the end of your query that got you that table. it will take a multi-value list and make it one value, you could also try |mvcombine delim="," typeCount
before the nomv
command to get a comma between them.
Oh no, I meant I want to get that table from repetitive fields. I'll update what I have. I tacked nomv after my table date category type count
, did not do anything
alright i understand now that you posted more.
what you need is |eval typeCount=Type+"-"+Count|stats values(typeCount) by date category
you can do some more evaling to split out typeCount to separate columns using mvindex
or rex
if you want.
Ah, I see... with the eval typeCount.. we are basically just combining the columns... if we were to do rex to split the columns, wouldn't it just go back to what I originally had? haha
it wouldn't go back to what you originally had because your date and category columns would still be condensed, which i believe is what you're looking for.
Alright, i'll attempt and keep posted. This solution is much harder for what I am trying to implement so it'll be a while (trying to use an input field to basically make a new column as a new filter every time a new parameter is checked)
I have looked at stats list(...) AS ... by Date, but I want to do more than just one merge. And I've tried doing separate stats or new search, not sure what to do now