How to display only column(s) that has value greater than 0

[Cbr1sg]

Hi all,
I have table looks like this

Column1,Column2,Column3,....,ColumnX
1,2,0,....5
1,0,5,....3
2,3,0,....0

Sometimes, depending on the search duration, the result could look like this

Column1,Column2,Column3,....,ColumnX
1,0,0,....3
2,0,0,....0
3,0,0,....3

I want to filter the column(s) that has only value "0" inside, so result will only show:
Column1,....ColumnX
1,....3
2,....0
3,....3

Is it possible?

Thanks

[HeinzWaescher · Accepted solution]

Try add this to your base search

| transpose
| addtotals
| search Total>0
| fields- Total
| transpose header_field=column
| fields - column

[Cbr1sg]

works perfectly as expected. Thank you very much!

[damien_chillet]

You could use the transpose command to remove columns with only zeros:
(Append to your search)

 | transpose 
 | eval total=0
 | foreach row* [eval total=total + '<<FIELD>>']
 | where total > 0
 |fields - total
 | transpose header_field=column
 | fields - column

[Cbr1sg]

The solution by HeinzWaescher is shorter and it's not allowed to accept more than 1 answer, but this works as well. Thank you very much!

[damien_chillet]

Yup I've made it a bit over complicated 😄

[Richfez]

And to be clear - you want to remove columnX for displaying if ALL columnX are zero, but display that column if any value in it is non-zero?

E.g.

1, 4, 0, 0
5, 0, 0, 4
2, 1, 0, 9
4, 0, 0, 0
0, 0, 0, 0

In that case, all 5 rows display, and columns 1, 2 and 4 are the only ones showing? is that right?

And did you specifically want a dot in the column, or how do you want to display it? Not at all? like...
1, 4, 0
5, 0, 4
2, 1, 9
4, 0, 0
0, 0, 0
(With column headers?)

[Cbr1sg]

Yes, you got my question correctly. The dot is just a way to tell that there are more than 3 columns (could be 4, 5 or 20) in the table.
Yes, the column header needs to be retained at the final result.

[cmerriman]

why is Column 1 in the final result? There is no 0 in the column.

[Cbr1sg]

because Column1 doesn't has only value "0" inside.
Column2 and 3 are filtered because all of the values inside those 2 columns are "0"