How do I format a number with commas in a column/field that has numbers and strings(using appendpipe)

[HattrickNZ]

How do I format a number with commas in a column/field that has numbers and strings(using appendpipe)

I have the following search:

 | makeresults 
  | eval data = "
  1    2017-12    A    155749    131033    84.1;
 2    2017-12    B    24869    23627    95;
 3    2017-12    C    117618    117185    99.6;
 " 
  | makemv delim=";" data 
  | mvexpand data
  | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
  | fields + date serial type attempts successfullAttempts sr 
  | rename date as _time 
  | search serial=*
  | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"] 

Which gives me the below:

    _time serial type attempts successfullAttempts sr
1   2017-12 1   A   155749  131033  84
2   2017-12 2   B   24869    23627   95
3   2017-12 3   C   117618  117185  99
4                             average sr 92.7

What I want to do is format the columns attempts and successfullAttempts, to have commas in their numbers. But because average sr falls in the successfullAttempts column I am having trouble. By placing the formatting before the append pipe, my "average sr" is removed or does not appear in the successfullAttempts column. **Can I make it so it stays there and the formatting?

...
      | fieldformat attempts=tostring(attempts,"commas")
      | fieldformat successfullAttempts=tostring(successfullAttempts,"commas")
      | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"] 

This gives me the following: (note the text "average sr" has been removed from the successfulAttempts column)

    _time serial type attempts successfullAttempts sr
1   2017-12 1   A   155749  131033  84
2   2017-12 2   B   24869    23627   95
3   2017-12 3   C   117618  117185  99
4                                        92.7

[493669]

If its not a specific requirement to have average sr present under successfullAttempts column then you can try to have average sr present under different column:

| makeresults 
   | eval data = "
   1    2017-12    A    155749    131033    84.1;
  2    2017-12    B    24869    23627    95;
  3    2017-12    C    117618    117185    99.6;
  " 
   | makemv delim=";" data 
   | mvexpand data
   | rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
   | fields + date serial  attempts successfullAttempts type sr 
   | rename date as _time 
   | search serial=*
   | appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval type="average sr"]
   | fieldformat attempts=tostring(attempts,"commas")
   | fieldformat successfullAttempts=tostring(successfullAttempts,"commas")

[HattrickNZ]

tks but I want to be able to have numbers and text in the same column, in general. the eval command mentioned in my comments does this. But your idea of re-ordering the columns and putting "average sr" in a column with strings is a good idea.

[HattrickNZ]

I think this is what I am looking for:

...
eval attempts=tostring(attempts,"commas") | 
eval successfullAttempts=tostring(successfullAttempts,"commas") | 
appendpipe [stats avg(sr) as sr | eval sr=round(sr,1) | eval successfullAttempts="average sr"]  | 

eval instead of fieldformat does not seem to present this problem. If anyone could suggest why that would be great? tks