How to create a column/field based on the max or value of another column

[HattrickNZ]

This is my sample search and corresponding output:

 | 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=*
| eval Q=attempts
| eval Q=if(Q==24869,"",Q)
| eval Q=if(Q==117618,"",Q)

OUTPUT

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

I want to be able to get the value of Q(e.g. 155749 ) and create a new field called W but all the values of W would be 155749. Basically, fill out all the values in W with one value and that value being 155749, the max of Q in this case.

Sample Output of what I would like

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

I was thinking of doing ... | eval W=max(Q) but that won't work but hopefully helps understand what I am trying to do.
maybe I need to use streamstats or other. I couldn't quite get it. Maybe I need a for loop? advice appreciated.

[niketn · Accepted solution]

@HattrickNZ add the following as your final pipe:

 <yourCurrentSearch>
| eventstats max(Q) as W

[p_gurav]

Can you try this:

| 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=*
 | eval Q=attempts
 | eval Q=if(Q==24869,"",Q)
 | eval Q=if(Q==117618,"",Q)
 | eventstats max(Q) AS W

[niketn]

@HattrickNZ if your issue is resolved, please accept @p_gurav 's answer as he had beaten me to it 😉