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?
| 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?
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.
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
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
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)