topic Re: Transpose 1 columns from table with 4 columns in Splunk Search

How to transpose one column from table with four columns?

verothor — Mon, 15 May 2023 13:45:52 GMT

Hi all, I have a table where I would like to transpose only one column with values from another column.

It looks like this..

Order	Date	Count	Shift
M5678	01/01/2023	12	A
M5678	01/01/2023	13	B
M1234	01/01/2023	13	A
M1234	01/01/2023	15	B

And I would like to achieve this..

Order	Date	A	B
M5678	01/01/2023	12	13
M1234	01/01/2023	13	15

Can someone please help with this.

Thank you so much

Re: Transpose 1 columns from table with 4 columns

acharlieh — Sat, 13 May 2023 18:45:17 GMT

I'll highly recommend @alacercogitatus "Lesser Known Search Commands" perennial .conf talk, because this is where I learned the {} trick with eval.

It looks like you want to create columns based on the value of the Shift column...
Which eval could do with the trick above, and then you can combine rows based on Order and Date parameters with stats:

... | eval SH_{Shift}=Count | stats values(SH_*) as * by Order Date

Alternatively, assuming that your given data is in fact the output of a stats command, another option would be instead of doing the stats, combine Order and Date into a single key with a delimiter, and then chart the count of your data by Order_Date and Shift.

You then use eval / rex / others to split Order and Date back out by the delimiter into separate columns.

But I'll leave that option as an exercise to the reader.

Re: Transpose 1 columns from table with 4 columns

verothor — Sat, 13 May 2023 21:02:22 GMT

Thanks @acharlieh your recommendation did the trick what I needed now.

Yes, your assumption is right the table comes from stats command, I will try to play with it more, but for now the first solution helped, thank you so much 🙂