How to compose a table with one search word is in row while the other search keyword is in column ?

[Jouman]

Hi all,

I am a fresher in Splunk.

Recently, I met an problems and would like to ask whether anyone has ideas.

I compose my spl code to generate this kind of table. 

city              produce name        count
city1            product 1            perchase count 1-1
city1            product 2            perchase count 1-2
city2            product 1            perchase count 2-1
city2            product 2            perchase count 2-2
city3            product 1            perchase count 3-1
city3            product 2            perchase count 3-1

But I would like to transfer the table into this kind of table.
product name          city1                                 city2                                 city 3
product 1         perchase count 1-1     perchase count 2-1     perchase count 3-1
product 2         perchase count 1-2     perchase count 2-2     perchase count 3-2

Do any experts have ideas how to implement SPL code to fulfill that?
I tried to use transpose column_name=city, but in vain. The output doesn't look as I expect.

Thank you so much ! 

[Jouman]

Hi all,

I already find the solution for my questions.

Thanks for all the reply you provided.

[efavreau]

@Jouman  If the answers helped you, please don't forget to "accept" the answer (so the community knows this has been answered) and click the thumbs up to give some karma! 🙂

[ITWhisperer]

| chart count by product, city

[Jouman]

Thanks! It works perfectly.

One more question to ask for help, assume all cities belong to the same Country,  and data are listed below already.

city              produce name        count
city1            product 1            perchase 1-1
city1            product 2            perchase 1-2
city2            product 1            perchase 2-1
city2            product 2            perchase 2-2
city3            product 1            perchase 3-1
city3            product 2            perchase 3-1

I can list count and its percentage % in each city sucessfully now, 
Is there any way to calculate the count in countrywide field, the percentage % in countrywide as well?
I tried to use transpose or eventstats after chart command but it doesn't work.

product name          city1                   city2                    city 3                           Countrywide
product 1    perchase1-1(%)  perchase 2-1(%)   perchase 3-1(%)  perchase1-1+perchase 2-1+perchase 3-1 (%)
product 2    perchase1-2(%)  perchase 2-2(%)   perchase 3-2(%)  perchase1-2+perchase 2-2+perchase 3-2 (%)
                              

Thank you so much.

[ITWhisperer · Accepted solution]

| chart count by product, city
| addtotals fieldname="Countrywide"

[Jouman]

Thanks! It works to calculate the count for countrywide area after I apply these code.

| chart count by Product City
| addtotals col=t labelfield="Product" label="City_Total" fieldname="Countrywide"

But after using these code, I can only list the count in chart format.

I was unable to list the percentage in chart format since addtotals will addup all fields in a row into "Countrywide" column.

Is there any way to transfer the count in each city and in countrywide into percentage value?
I tried these 2 paragraphs code but none is working.

1)
| chart limit=0 count by Product City
| addtotals col=t labelfield="Product" label="City_Total" fieldname="Countrywide"
| foreach *
     [| eval city_percent=round(100*<<FIELD>>/City_Total,1)]
| chart limit=0 values(city_percent) by Product City

OR 

2)
| chart limit=0 count by Product City
| eval Countrywide=0
| foreach *
       [| eval Countrywide=Countrywide+<<FIELD>>]
| addtotals col=t labelfield="Product" label="City_Total"
| foreach *
      [| eval <<FIELD>>=round(<<FIELD>>*100/City_Total,1)]

Thank you.