Splunk Search
Highlighted

How to transpose data and create a new row?

New Member

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.

Tags (2)
0 Karma
Highlighted

Re: How to transpose data and create a new row?

Legend

@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
0 Karma
Highlighted

Re: How to transpose data and create a new row?

New Member

Thanks! It looks good!

0 Karma
Highlighted

Re: How to transpose data and create a new row?

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

View solution in original post

0 Karma
Highlighted

Re: How to transpose data and create a new row?

New Member

Great answer! Thanks!

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.