How to combine two fields values into one

[yvassilyeva]

Hi,

I have the following table:

status                                          count

CANCELLED                                5

Cancelled                                     10

RESOLVED                                    3

Resolves                                        3

And i would like to combine the same name field values despite the letter cases like this:

status                                         count
Cancelled                                     15

Resolved                                        6

Thank you in advance.

[richgalloway · Accepted solution]

There are a couple of ways to do that.  The most common is to use a case statement to normalize the values.

| eval status=case(status="CANCELLED" OR status="Cancelled","Cancelled", status="RESOLVED" OR status="Resolved", "Resolved", 1==1, status)

Another way is to normalize by making all values have the same case.

| eval status=lower(status)

Do either of these before the stats command.

[yvassilyeva]

Thank you. That is perfect

[ITWhisperer]

| eval status=substr(status,1,1).lower(substr(status,2))

[yvassilyeva]

Thank you for a quick response. That also worked!