I need some help grouping and transposing some data. The search below gives me the data but now I want to group it and transpose one of the fields with it's data.
....
| stats count by Stage, Model,Region
| table Region,Model,Stage,count
| sort Region,Model,Stage
Region | Model | Stage | count
East | Blue | Phase1 | 5
East | Blue | Phase2 | 3
East | Red | Phase1 | 2
East | Red | Phase2 | 7
West | Blue | Phase1 | 6
West | Blue | Phase2 | 9
West | Red | Phase1 | 7
West | Red | Phase2 | 8
I want it to look like
Region | Model | Phase1 | Phase2
East | Blue | 5 | 3
........| Red | 2 | 7
West | Blue | 6 |9
........ | Red | 7 | 8
Sorry for my table formatting as I was sure how to do it nicely.
something like this should help if added to the end:
...|eval{Stage}='count'|fields - Stage count|stats values(*) as * by Region Model
something like this should help if added to the end:
...|eval{Stage}='count'|fields - Stage count|stats values(*) as * by Region Model
Thank you, that did the trick and saved me a bunch of time. I will add this to my toolbox of SPL.
Just add this to the bottom:
| stats list(*) AS * BY Region
Thanks for the suggestion, while this produced the results it did not transpose the columns.