topic Re: How to group by country and concatenate the cities into one row? in Splunk Search

How to group by country and concatenate the cities into one row?

maximusdm — Mon, 17 Jul 2017 19:07:33 GMT

giving the folowing scenario:

...
| table Country City Population

>     Country       City        Population
>     Spain     Madrid      2,456,000
>     Spain     Barcelona   3,222,000
>     Spain     Valencia    1,111,000
>     England       London      9,222,000
>     England       Oxford      1,211,000

How can I display the same results but grouping by Country and concatenating the cities and population?
Something like:

Spain   Madrid(2,456,000), Barcelona(3,222,000), Valencia(1,111,000)
England London(9,222,000), Oxford(1,211,000)

Thanks for the help

Re: How to group by country and concatenate the cities into one row?

somesoni2 — Mon, 17 Jul 2017 19:10:18 GMT

Try like this

... | table Country City Population
| eval Population=City."(".Population.")"
| stats values(Population) as Population by Country delim=","
| nomv Population

Re: How to group by country and concatenate the cities into one row?

maximusdm — Mon, 17 Jul 2017 19:36:57 GMT

wow thanks I was doing stats by Country but not getting anywhere. Never heard of nomv command.
Thank you so much.