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
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"]
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"]
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?
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>>)]
what about this FIELD ..?
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
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"
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]
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.