How to display top 5 and replace the rest with others?

[LearningGuy]

How to display top 10 and replace the rest with others?
I tried using   top limit 5 with userother, but the number didn't match and showed other fields like count, percent and _tc. 
This is just an example.  I have a lot of fields and rows in real data
 Thank  you for your help

| addcoltotals labelfield=Name
| top limit=5 userother=t Name Score       ==>   number didn't match

Before

Expense	Name	Score
1	Rent	2000
2	Car	1000
3	Insurance	700
4	Food	500
5	Education	400
6	Utility	200
7	Entertainment	100
8	Gym	70
9	Charity	50
10	Total	5020

After

Expense	Name	Score
1	Rent	2000
2	Car	1000
3	Insurance	700
4	Food	500
5	Education	400
6	Others	420
7	Total	5020

[PickleRick]

Why do you say the numbers don't match?

Instead of 200, 100, 70, 50 you have one row with value of 420.

[LearningGuy]

Hello,

I meant that if I used the following, the "other" won't match the rest of the numbers.   Thanks

| top limit=5 userother=t Name Score 

[ITWhisperer · Accepted solution]

| sort 0 -Score
| streamstats count as row sum(Score) as running
| eventstats sum(Score) as total
| where row <= 7
| eval Score=case(row == 6, total - running + Score, row == 7, total, true(), Score)
| eval Name=case(row == 6, "Other", row == 7, "Total", true(), Name)
| fields - row running total

[LearningGuy]

Hello,

If I have additional fields (Score2, Score3), should I add those fields in the search like below?
I am not how to do it on eval Name. Please suggest. Thank you for your help

| streamstats count as row sum(Score) as running, sum(Score2) as running2, sum(Score3) as running3
| eventstats sum(Score) as total, sum(Score2) as total2, sum(Score3) as total3)
| where row <= 7
| eval Score=case(row == 6, total - running + Score, row == 7, total, true(), Score)
| eval Score2=case(row == 6, total2 - running2 + Score2, row == 7, total2, true(), Score2)
| eval Score3=case(row == 6, total3 - running3 + Score3, row == 7, total3, true(), Score3)

| eval Name=case(row == 6, "Other", row == 7, "Total", true(), Name)
| fields - row running running2 running3  total total2 total3

[ITWhisperer]

Yes, except you haven't shown what you would be sorting by. The original ask was to sort by Score descending. How would you sort if you also had Score2 and Score3?

[LearningGuy]

Hello,

I accepted your suggestion as solution.  
I  would sort by Score if I had Score2 and Score 3

I made some modifications. I used addcoltotals, added "total other", and added Score 2 and Score 3.

The only problem is I don't know where Expense no "21"  came from.   

Can you take a look at my search below and see if it looks correct?
Thank you for your help

| makeresults format=csv data="Expense,Name,Score,Score2,Score3  
1,Rent,2000,20000,200000 
2,Car,1000, 10000,100000
3,Insurance,700,7000,70000
4,Food,500,5000,50000
5,Education,400,4000,40000
6,Utility,200,2000,30000
7,Entertainment,100,1000, 10000
8,Gym,70,700,70000
9,Charity,50,500,5000"
| sort 0 -Score
| streamstats count as row sum(Score) as running, sum(Score2) as running2, sum(Score3) as running3
| eventstats count(Name) as total_name, sum(Score) as total, sum(Score2) as total2, sum(Score3) as total3
| where row <= 6
| eval Score=case(row == 6, total - running + Score, true(), Score)
| eval Score2=case(row == 6, total2 - running2 + Score2, true(), Score2)
| eval Score3=case(row == 6, total3 - running3 + Score3, true(), Score3)

| eval other_name_ct = total_name - 5
| eval Name=case(row == 6, "Other(". other_name_ct.")", true(), Name)
| addcoltotals labelfield=Name

| fields - row running running2 running3 total total2 total3

[ITWhisperer]

21 = 1+2+3+4+5+6 i.e. it comes from your addcoltotals - try this

| addcoltotals labelfield=Name Score*