How to merge text in Splunk table?

[ashanka]

Have a table like this , how

Type Country

Reporting US
reporting CAN
Reporting IND
COnsolidated US
Consolidate CAN
Consolidated IND

How do i display as

Type Country

Reporting US
CAN
IND
Consolidated US
CAN
IND

[anmolpatel]

@ashanka as such:

| makeresults 
| eval _raw = "Type Country
Reporting US
reporting CAN
Reporting IND
COnsolidated US
Consolidate CAN
Consolidated IND" 
| multikv forceheader=1 
| eval Type_new = upper(substr(Type, 0, 1)) + lower(substr(Type, 2)) 
| eval Type = case(match(Type_new, "Reporting"), "Reporting", match(Type_new, "Consolidate(d)?"), "Consolidated")
| stats list(Country) as Country By Type

This will return:

[manjunathmeti]

Try this. This will display results exactly as you want.

| eval Type=if(Country="US", Type, Country), Country=if(Country="US", Country, "") 
| table Type, Country

If you want to group Country by Type, then use stats.

| stats values(Country) as Country by Type

Sample query:

| makeresults 
| eval _raw=" Type Country
Reporting US
reporting CAN
Reporting IND
Consolidated US
Consolidate CAN
Consolidated IND" 
| multikv forceheader=1 
| eval Type=if(Country="US", Type, Country), Country=if(Country="US", Country, "") 
| table Type, Country