grouping and counting rows with respect to 2 values

[Andr3A]

Hi everyone, after a search with some eval e rex commands, I end up in a table like this:

ID --- FIELD(1) --- FIELD(2) --- FIELD(3) --- ... --- FIELD(9)
id(1) --- OK --- Alert --- Alert --- OK --- ... --- OK

id(2) --- OK --- OK --- OK --- Alert --- ... --- OK

id(3) --- Alert --- Alert --- OK --- ... --- OK

.

.

.

id(10000) --- OK --- Alert --- OK --- OK --- ... --- Alert

So I have a 10000x10 table where the first field represents an id for the row, while the other fields can take only 2 values (OK or Alert).

I'd like to get a summury with respect to the field ID like this:

                        OK                 ALERT

FIELD1

FIELD2

FIELD3

.

.

.

FIELD9

Where for each cell [FIELD(i), OK] I have the sum of the OKs in the column FIELD(i) and for each cell [FIELD(i), Alert] I have the sum of the Alerts in the column FIELD(i) ... obviously the sum of each row has to be equal to 10000.

If you have some ideas you are welcome, thx 🙂

[ITWhisperer · Accepted solution]

Transpose might help

| transpose
| eval TotalOK = 0
| eval TotalAlert = 0
| foreach "row*" [ eval TotalOK = TotalOK + if('<<FIELD>>'="OK",1,0) | eval TotalAlert = TotalAlert + if('<<FIELD>>'="Alert",1,0) ]
| fields column, TotalOK, TotalAlert

[Andr3A]

It works perfectly, thx for the help!

ps just for Readers: you need to use the "| transpose 0" in order not to limit the number of rows transposed.