How to find the max value based on the rows?

[harishalipaka]

Hi all,
I want max value by row wise not max (field name)

**Date**           **shiftA**     **shiftB**     **shiftC**    **Max**
12/08                     102                   10                     200               200
25/08                     240                   102                   52                  240

I want like this. Please help for this. I want it urgent.
Thanks

[harishalipaka · Accepted solution]

This is my answer...
| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date | appendcols [ | makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*| stats max(shiftA) AS Total ,max(shiftB) as ttt ,max(shiftC) as cc | transpose 10 |rename "row 1" as "All"]

[HattrickNZ]

How do you actually get the max column other than typing all that out after the below?

This is what the user has. How do we add a max field to it?
| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date

Or in other words is there a way to dynamically get the max without having to specify the field names? Or what do I have to add here, dynamically, to get the max?

[niketn]

Hi, you would need to use foreach command to iterate through your fields in every row to compute Max. Following is the run anywhere search which mocks the data in this example and applies foreach. Pipes till | table Date shift* generate the mock data. You need the two pipes after that.

| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append 
    [| makeresults
     | eval  Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| table Date shift*
| eval Max=0
| foreach shift*
    [eval Max=case(Max>=<<FIELD>>,Max,true(),<<FIELD>>)]

[harishalipaka]

what about this FIELD ..?

[harishalipaka]

thanks for your replay sorry i want like this ..below is my query (dates not unique chaged based on query)

| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date

[niketn]

As per updated info, following query generates Data in required format.

| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append 
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append 
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date

Pipe the following to your transpose to generate required stats:

| foreach */*
     [eval <<FIELD>>=case(len('<<FIELD>>')=3,'<<FIELD>>',len('<<FIELD>>')=2,"0".'<<FIELD>>',len('<<FIELD>>')=1,"00".'<<FIELD>>')]
| foreach */*
     [eval key_<<FIELD>>='<<FIELD>>'."-".Shift]
| fields key_*
| eventstats max(*) as *
| head 1
| transpose column_name="Date"
| eval Date=replace(Date,"key_","")
| eval "row 1"=split('row 1',"-")
| eval Value=mvindex('row 1',0)
| eval Shift=mvindex('row 1',1)
| fields - "row 1"

[cmerriman]

with your makeresults, if you're doing a transpose before trying to calculate the max, you can try this:

| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append 
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append 
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date
 | appendpipe
     [eventstats max(*) as * |dedup column]

[niketn]

Would it be possible for you to give us your existing query? What is the final transfoming command you have used to build your table?
I am converting answer to comment. Since this is not resolved yet and we would require more details from your end. You can perform a transpose on above data so that above answer works for you. However, I feel it is better if we handled it directly.