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
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.
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.
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 🙂