The original data :
_time reg exp raw
2019-09-20 A 1 100
2019-09-20 B 2 200
2019-09-20 C 3 300
2019-09-20 D 1 100
2019-09-20 E 2 200
2019-09-20 F 3 300
2019-09-19 A 1 100
2019-09-19 B 2 200
2019-09-19 C 3 300
2019-09-19 D 1 100
2019-09-19 E 2 200
2019-09-19 F 3 300
How to make a table as following: only show exp values with fields name as reg and have percentages:
_time per A B C D E F
2019-09-20 0.01 1 2 3 1 2 3 //exp values
2019-09-19 0.01 1 2 3 1 2 3 //exp values
per = sum(exp values)/sum(raw values) example: (1+2+3+1+2+3)/(100+200+300+100+200+300)
hope i understand your requirement,
try this anywhere:
| makeresults count=1
| eval data = "2019-09-20 A 1 100;;;2019-09-20 B 2 200;;;2019-09-20 C 3 300;;;2019-09-20 D 1 100;;;2019-09-20 E 2 200;;;2019-09-20 F 3 300;;;2019-09-19 A 1 100;;;2019-09-19 B 2 200;;;2019-09-19 C 3 300;;;2019-09-19 D 1 100;;;2019-09-19 E 2 200;;;2019-09-19 F 3 300"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<time>[^\s]+)\s+(?<reg>[^\s]+)\s+(?<exp>[^\s]+)\s+(?<raw>[^\s]+)"
| table time reg exp raw
| rename COMMENT as "the above generates data below is the solution"
| eventstats sum(raw) as total_raw sum(exp) as total_exp by time
| eval percent = round(total_exp / total_raw, 2)
| eval {reg} = exp
| stats values(*) as * by time
hope it helps
hope i understand your requirement,
try this anywhere:
| makeresults count=1
| eval data = "2019-09-20 A 1 100;;;2019-09-20 B 2 200;;;2019-09-20 C 3 300;;;2019-09-20 D 1 100;;;2019-09-20 E 2 200;;;2019-09-20 F 3 300;;;2019-09-19 A 1 100;;;2019-09-19 B 2 200;;;2019-09-19 C 3 300;;;2019-09-19 D 1 100;;;2019-09-19 E 2 200;;;2019-09-19 F 3 300"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<time>[^\s]+)\s+(?<reg>[^\s]+)\s+(?<exp>[^\s]+)\s+(?<raw>[^\s]+)"
| table time reg exp raw
| rename COMMENT as "the above generates data below is the solution"
| eventstats sum(raw) as total_raw sum(exp) as total_exp by time
| eval percent = round(total_exp / total_raw, 2)
| eval {reg} = exp
| stats values(*) as * by time
hope it helps
what a trick! this is insane. Thank you so much!
Thanks.
@jenniferhao try ... | table _time A B C D E F ...
Thanks, Adonio. It works well now.
Thanks, Adonio. It works. If I only want to show _time A B C D E F percent. How Can I do this?