How to replace values by its rank in a two dimensional chart?

[ynepyyvoda]

As example I have a search:

... | chart avg(value) as Value by country, supplier

this will result in a two dimensional table

| |Supplier1 | Supplier2 | Supplier3 |
| US | 23 | 21 | 42 |
| UK | 30 | 24 | 26 |
| UK | 33 | 44 | 21 |

I need to replace those values by corresponding supplier rank per country like

| |Supplier1 | Supplier2 | Supplier3 |
| US | 2 | 3 | 1 |
| UK | 1 | 3 | 2 |
| UK | 2 | 1 | 3 |

How can it be done?

[woodcock]

First, fake the data:

|noop|stats count AS country
|eval country="US,UK,VW"|makemv delim="," country|mvexpand country
|eval supplier="Supplier1,Supplier2,Supplier3"|makemv delim="," supplier|mvexpand supplier
|eval value=case((country="US" AND supplier="Supplier1"), "23",
                 (country="US" AND supplier="Supplier2"), "21",
                 (country="US" AND supplier="Supplier3"), "42",
                 (country="UK" AND supplier="Supplier1"), "30",
                 (country="UK" AND supplier="Supplier2"), "24",
                 (country="UK" AND supplier="Supplier3"), "26",
                 (country="VW" AND supplier="Supplier1"), "33",
                 (country="VW" AND supplier="Supplier2"), "44",
                 (country="VW" AND supplier="Supplier3"), "21")

Then, do the magic:

| stats avg(value) AS Value BY country supplier
| sort 0 - Value
| streamstats current=t count AS RankNoTies BY country
| eventstats min(RankNoTies) AS Rank BY country Value
| fields - RankNoTies
| chart first(Value) first(Rank) BY country supplier
| rename first(Rank)* AS Rank* first(Value)* AS Value*

Do note that this solution also accounts for ties.

[sundareshr]

See if this gives you what you're looking for (replace you chart command with this)

... | stats count by country supplier | sort country - count | streamstats count by country | chart values(count) as rank by country supplier