Transpose without aggregate column

[matimat]

Hi,

I want to show the elapsed time of each event return by my query .

The elapsed time is on field name execTime, the event name on field Title.

I used a bar chart stacked to show the result.

My query is : 

index=blabla | table title, execTime | transpose 0 header_field=title include_empty=true

The issue is transpose command aggregate all title with the same value. I won't that.

Before transpose I have this : 

Title	Duration
T1	2
T2	5
T3	1
T2	6
T4	12

After transpose I have this (T2 is agregate with sum) : 

T1	T3	T2	T4
2	1	11	12

But I want this : 

T1	T2	T3	T2	T4
2	5	1	6	12

Regards

[thambisetty]

@niketn  may be you can remove sort to give expected result.

[niketn]

@thambisetty streamstats works in streaming manner, it requires sorting to be in place for reset_on_change to work.

[thambisetty]

@niketn 
even by clause is not required. 🙂

| makeresults | eval _raw="Title,Duration
T1,2
T2,5
T3,1
T2,6
T4,12"
| multikv forceheader=1
| table Title,Duration

| streamstats count as padSpace
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields - padSpace
| transpose 0 header_field=Title column_name=Title

[niketn]

@thambisetty this will end up padding all rows endlessly. If you have 100 rows... 100th row will have 100 spaces padded.

My Solution pads space only if there is a duplicate. If the same title repeats 3 times it pads three spaces. But if there are 100 rows with 99 rows and 1 duplicate your solution will pad each column with an incremental space.

I replaced space " " with hyphen "-", and you can see for yourself what your query is doing vs. mine 😉

| makeresults count=100
| eval Title="T"
| streamstats count as sno
| eval Title=Title.sno
| eval Duration=substr(tostring(random()),1,2)
| table Title,Duration 
| streamstats count as padSpace 
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1),"-"),"\d","")."".Title 
| fields - padSpace 
| transpose 0 header_field=Title column_name=Title

[niketn]

@thambisetty this will end up padding all rows endlessly. If you have 100 rows... 100th row will have 100 spaces padded.

My Solution pads space only if there is a duplicate. If the same title repeats 3 times it pads three spaces. But if there are 100 rows with 99 rows and 1 duplicate your solution will pad each column with an incremental space.

I replaced space " " with hyphen "-", and you can see for yourself what your query is doing vs. mine 😉

| makeresults count=100
| eval Title="T"
| streamstats count as sno
| eval Title=Title.sno
| eval Duration=substr(tostring(random()),1,2)
| table Title,Duration 
| streamstats count as padSpace 
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1),"-"),"\d","")."".Title 
| fields - padSpace 
| transpose 0 header_field=Title column_name=Title

Here is what my query will do for same scenario:

| makeresults count=100
| eval Title="T"
| streamstats count as sno
| eval Title=Title.sno
| eval Duration=substr(tostring(random()),1,2)
| table Title Duration 
| sort Title 
| streamstats count as padSpace by Title reset_on_change=true
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1),"-"),"\d","")."".Title
| fields - padSpace
| transpose 0 header_field=Title column_name=Title

[niketn · Accepted solution]

[UPDATED ANSWER]

@matimat Since you do not need to change the order of sorting of results. You may have use different streamstats. Try the following which will not require sorting (I have included by clause in streamstats to detect a change in Title):

index=blabla
| tableTitle,Duration
| streamstats count as padSpace by Title
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields Title Duration
| transpose 0 header_field=title include_empty=true

Without Sorting

| makeresults 
| fields - _time 
| eval data="T1,2;T2,5;T3,1;T2,6;T4,12" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Title=mvindex(data,0),Duration=mvindex(data,1) 
| fields - _data 
| table Title Duration 
| streamstats count as padSpace by Title
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields Title Duration
| transpose 0 header_field=Title column_name=Title

____________________________________________________________

With Sorting

The above search sorts title and count of repetition of the same title using streamstats.
The eval prefixes space character for each repetition of Title so that although Table Title look the same they are actually prefixed with one of more spaces depending on repetition.

Please try out and confirm. Following is a run anywhere example as per the data/field in your question:

| makeresults 
| fields - _time 
| eval data="T1,2;T2,5;T3,1;T2,6;T4,12" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Title=mvindex(data,0),Duration=mvindex(data,1) 
| fields - _data 
| table Title Duration 
| sort Title 
| streamstats count as padSpace by Title reset_on_change=true
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields - padSpace
| transpose 0 header_field=Title column_name=Title

[matimat]

Thank you for your response, if I understand,  the solution is to change name identical with space.

My column must not be sort. For now only the solution of Karma match with my wish.

[niketn]

@matimat Try the updated answer without Sort if it fits your needs! 🙂