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
Try add this to your base search
| transpose
| addtotals
| search Total>0
| fields- Total
| transpose header_field=column
| fields - column
Try add this to your base search
| transpose
| addtotals
| search Total>0
| fields- Total
| transpose header_field=column
| fields - column
works perfectly as expected. Thank you very much!
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
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!
Yup I've made it a bit over complicated 😄
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?)
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.
why is Column 1
in the final result? There is no 0 in the column.
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"