Hi all,
I have a table data looks like:
Time Type Count
-------- ---------- --------------
2018/01 A 10
2018/01 B 5
2018/02 A 20
2018/02 B 10
... ... ...
I want to transpose it and create a new row by B divide by A :
Type 2018/01 2018/02 ...
A 10 20
B 5 10
C 0.5 0.5
I tried | transpose 0 header_field=Time
but it does not work.
hi @kevingo75
| makeresults | eval Time ="2018/01", Type="A", Count=10 |append [| makeresults | eval Time ="2018/01", Type="B", Count=5] |append [| makeresults | eval Time ="2018/02", Type="A", Count=20]|append [| makeresults | eval Time ="2018/02", Type="B", Count=10] |fields - _time |chart values(Count) over Time by Type |eval C=B/A |transpose header_field=Time
hi @kevingo75
| makeresults | eval Time ="2018/01", Type="A", Count=10 |append [| makeresults | eval Time ="2018/01", Type="B", Count=5] |append [| makeresults | eval Time ="2018/02", Type="A", Count=20]|append [| makeresults | eval Time ="2018/02", Type="B", Count=10] |fields - _time |chart values(Count) over Time by Type |eval C=B/A |transpose header_field=Time
Great answer! Thanks!
@kevingo75, instead of transpose try the following chart command on existing table as per your question:
<yourCurrentSearchResultingInFirstTable>
| chart sum(Count) as count by Type Time
Following is a run anywhere search based on the sample data provided in the question (commands from |makeresults
till the |table
command generate the dummy data as per the question:
| makeresults
| eval data="2018/01,A,10;2018/01,B,5;2018/02,A,20;2018/02,B,10"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval Time=mvindex(data,0),Type=mvindex(data,1),Count=mvindex(data,2)
| table Time Type Count
| chart sum(Count) as count by Type Time
Thanks! It looks good!