topic Re: Find maximum value in one column where a second column meets criteria in Splunk Search
https://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528063#M149078
<P>No that's not quite it. This image shows how the first 0 in the achieved column effectively locks the max_achieved to the level of the previous row, where the last '1' was</P><P>Like this</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="bowesmana_0-1604554049727.png" style="width: 400px;"><img src="https://community.splunk.com/t5/image/serverpage/image-id/11654i190EBA94DA698EA9/image-size/medium?v=v2&px=400" role="button" title="bowesmana_0-1604554049727.png" alt="bowesmana_0-1604554049727.png" /></span></P><P>Essentially what I am trying to do is find the highest contiguously attained level (ml). So in the above image, even though there were 1s up to ml=8, the presence of the 0 at ml=4 means that all ml>3 are meaningless.</P><P> </P>Thu, 05 Nov 2020 05:29:28 GMTbowesmana2020-11-05T05:29:28ZFind maximum value in one column where a second column meets criteria
https://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528040#M149068
<P><SPAN>I believe as with all things Splunk, there is more than one way to solve this</SPAN></P><P><SPAN>My data consists of this</SPAN></P><P> </P><LI-CODE lang="markup">| makeresults
| eval ml=mvrange(1,4)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved</LI-CODE><P> </P><P>What I want is to find the highest value of ml where achieved=1 and where there has not been achieved=0 in a lower value of ml.</P><P>I have worked out that this works, but I would like to see if there is a different solution as this seems a bit overly complicated</P><P> </P><LI-CODE lang="markup">| makeresults
| eval ml=mvrange(1,4)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved
| eval x=if(achieved=0,0,null)
| filldown x
| eventstats max(eval(if(isnull(x),ml,null))) as y
| head 1
| eval max_ml=if(isnull(y),0,y)</LI-CODE><P> </P><P>where max_ml will give the desired outcome.</P><P>Anyone else see an alternative solution. Note mvrange size can be any size, so not just 3 values.</P>Thu, 05 Nov 2020 00:02:32 GMThttps://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528040#M149068bowesmana2020-11-05T00:02:32ZRe: Find maximum value in one column where a second column meets criteria
https://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528045#M149070
<P>Does this work?</P><LI-CODE lang="markup">| makeresults
| eval ml=mvrange(1,10)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved
| eventstats max(eval(if(achieved==1,ml,null()))) as max_achieved_ml ,min(eval(if(achieved==0,ml,null()))) as min_unachieved_ml</LI-CODE><P> </P><P>eventstats can be replaced with stats if you dont need other fields. </P>Thu, 05 Nov 2020 01:22:55 GMThttps://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528045#M149070renjith_nair2020-11-05T01:22:55ZRe: Find maximum value in one column where a second column meets criteria
https://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528063#M149078
<P>No that's not quite it. This image shows how the first 0 in the achieved column effectively locks the max_achieved to the level of the previous row, where the last '1' was</P><P>Like this</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="bowesmana_0-1604554049727.png" style="width: 400px;"><img src="https://community.splunk.com/t5/image/serverpage/image-id/11654i190EBA94DA698EA9/image-size/medium?v=v2&px=400" role="button" title="bowesmana_0-1604554049727.png" alt="bowesmana_0-1604554049727.png" /></span></P><P>Essentially what I am trying to do is find the highest contiguously attained level (ml). So in the above image, even though there were 1s up to ml=8, the presence of the 0 at ml=4 means that all ml>3 are meaningless.</P><P> </P>Thu, 05 Nov 2020 05:29:28 GMThttps://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528063#M149078bowesmana2020-11-05T05:29:28ZRe: Find maximum value in one column where a second column meets criteria
https://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528067#M149080
<P>Sorry, missed that part.</P><P>What about this based on the test data ? </P><LI-CODE lang="markup">| makeresults
| eval ml=mvrange(1,10)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved
| eventstats min(eval(if(achieved==0,ml-1,null()))) as max_ml</LI-CODE><P> </P>Thu, 05 Nov 2020 07:04:21 GMThttps://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528067#M149080renjith_nair2020-11-05T07:04:21ZRe: Find maximum value in one column where a second column meets criteria
https://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528212#M149138
<P><LI-USER uid="136781"></LI-USER> </P><P>Almost got it - yes, clearer and simpler than my original. However, your eventstats on its own is not quite sufficient to handle the all 1 case, so this is needed</P><LI-CODE lang="markup">| eventstats min(eval(if(achieved==0,ml-1,null()))) as max_ml max(ml) as highest_ml
| eval max_ml=if(isnull(max_ml),highest_ml,max_ml)</LI-CODE><P>to handle the null max_ml when all achieved values are 1.</P><P> Thanks for your help - I was stuck in too many dimensions to see the simplicity</P>Thu, 05 Nov 2020 20:37:05 GMThttps://community.splunk.com/t5/Splunk-Search/Find-maximum-value-in-one-column-where-a-second-column-meets/m-p/528212#M149138bowesmana2020-11-05T20:37:05Z