Hi,
I have the data in the below format i.e i have calculated base on Type A,B,C per month and the data looks like
Jan 2017,A, 1,2
Jan 2017,B, 3,4
Jan 2017,C, 5,6
Feb 2017,A, 7,8
Feb 2017,B, 10,12
Feb 2017,C, 13,16
Mar 2017,A, 1,2
Mar 2017,B, 3,4
Mar 2017,C, 5,6
I want to convert this data in to the below format:
Jan 2017,Feb 2017,Mar 2017
A, 1,2 7,8 1,2
B, 3,4 10,12 3,4
C, 5,6 13,16 5,6
is there any simple method to do so?
Thanks for your help.
Like this:
| makeresults
| eval raw="Jan 2017,A, 1,2::Jan 2017,B, 3,4::Jan 2017,C, 5,6::Feb 2017,A, 7,8::Feb 2017,B, 10,12::Feb 2017,C, 13,16::Mar 2017,A, 1,2::Mar 2017,B, 3,4::Mar 2017,C, 5,6"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<Month>[^,]+),(?<Type>[^,]+),(?<Value>.*)$"
| fields - _*
| rename COMMENT AS "Everything above fakes your data; everything below is your solution"
| chart list(Value) AS Value OVER Type BY Month
At this point, you may be interested in my (as yet unaccepted) answer here:
Hi Woodcock,
I have the below query
index=guistats_summary DIVISION_CD=E GUI_Enable=Y COUNTRY_CD="EE" OR COUNTRY_CD="LT" OR COUNTRY_CD="LV" OR COUNTRY_CD="LB" | bin span=1mon _time | eval GUI_Count=if(GUI_IND="Y",Total_Count,0), Non_GUI_Count=if(GUI_IND="N",Total_Count,0)|rename _time as Date|eval Date=strftime(Date,"%b-%y")|chart sum(GUI_Count) as GUI_Count,sum(Non_GUI_Count) as Non_GUI_Count OVER COUNTRY_CD By Date
which give the below results
COUNTRY_CD GUI_Count: Feb-17 GUI_Count: Jan-17 GUI_Count: Mar-17 Non_GUI_Count: Feb-17 Non_GUI_Count: Jan-17 Non_GUI_Count: Mar-17
EE 344 237 249 262 304 251
LB 2576 2173 2800 251 9 2
LT 367 347 411 292 489 354
LV 587 449 449 387 108 75
is it possible to arrange the column so that GUI_Count and Non GUI for same months comes together so that i dont need to run tables /fields command?
I do not understand at all. It might help if you just extend the original fake data to be "four components" as you put it (which I also do not understand). It would be even better if you modify my "fakes your data" stuff so that it generates fake data more like you are saying you need.
|table Value _time host counter
value _time host counter
42 somedate test idle
43 somedate test notidle
52 anotherdate test idle
55 anotherdate test notidle
i want to see below, how to do it with chart..
host _time idle notidle
test somedate 42 43
test anotherdate 52 55