topic Re: Transpose without aggregate column in Dashboards & Visualizations

Transpose without aggregate column

matimat — Sat, 05 Sep 2020 14:56:03 GMT

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

Re: Transpose without aggregate column

niketn — Sun, 06 Sep 2020 03:34:42 GMT

[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):

Without Sorting

____________________________________________________________

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:

Re: Transpose without aggregate column

thambisetty — Sat, 05 Sep 2020 18:04:54 GMT

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

Re: Transpose without aggregate column

niketn — Sat, 05 Sep 2020 18:06:07 GMT

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

Re: Transpose without aggregate column

thambisetty — Sat, 05 Sep 2020 18:08:05 GMT

@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

Re: Transpose without aggregate column

niketn — Sat, 05 Sep 2020 18:18:11 GMT

@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 😉

Here is what my query will do for same scenario:

Re: Transpose without aggregate column

niketn — Sat, 05 Sep 2020 18:15:57 GMT

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

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

Re: Transpose without aggregate column

matimat — Sat, 05 Sep 2020 22:02:08 GMT

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.

Re: Transpose without aggregate column

niketn — Sun, 06 Sep 2020 03:35:24 GMT

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