How to transpose data and create a new row?

[kevingo75]

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.

[harishalipaka · Accepted solution]

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

[kevingo75]

Great answer! Thanks!

[niketnilay]

@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

[kevingo75]

Thanks! It looks good!