How to merge cells in a table based on value

[szabados]

I have a table like this:

col1        | col2     | col3
samevalue   | value1   | value2
samevalue   | value3   | value4
samevalue   | value6   | value5
samevalue2  | value1   | value2
samevalue 2 | value3   | value4
samevalue 2 | value6   | value5

What I want to achieve is to merge the cells in col1, where the value is the same as the cell above, like this:

col1        | col2     |  col3
samevalue   | value1   | value2
            | value3   | value4
            | value6   | value5
samevalue2  | value1   | value2
            | value3   | value4
            | value6   | value5

I'm producing the table with pivot, but in that column I need some sort of the grouping as I would have with stats, but without messing up the relation between the rows in other columns

Edit: my padding was broken when I posted the question, sorry

[sundareshr]

Try this

your base search | stats values(col2) as col2 values(col3) as col3 by col1

[kbarker302 · Accepted solution]

If I understand correctly, you can do this with a combination of streamstats and eval. In the below example, streamstats appends the previous col1 value to the current record. The eval statement then sets col1 to blank if the value hasn't changed.

your base search
| sort col1 col2 
| streamstats current=f window=1 global=f last(col1) as last_col1 
| eval col1=if(col1==last_col1,'',col1)
| table col1 col2 col3

This link gave me some guidance on using the streamstats statement:

https://answers.splunk.com/answers/87382/comparing-fields-with-previous-events.html

[adam_reber]

Just wanted to say thank you for this clever solution, it helped me immensely today!

[szabados]

Thanks!

Not exactly the same approach as I head in my head, but I like it a lot.
I learned something new again.

[kbarker302]

Thank you - I used custom JavaScript to solve a similar issue previously, but your question made me reconsider whether there was a way to do it purely with SPL.