topic Re: rangemax function - 2nd highest value in QlikView

rangemax function - 2nd highest value

Thu, 11 Apr 2013 15:04:43 GMT

I'm trying to sort a bunch of fields to create a stat-line of sorts from a basketball boxscore. Basically what I want it to do is return the best and 2nd best stat from a range of 4 stats. So if the 4 stats are PTS, REB, AST, STL, my formula to get the best stat would be:

rangemax(PTS+.1,REB+.01,AST+.001,STL+.0001) --> the .1, .01 etc. is there to make sure I never have the same value in multiple fields

What I need now is a formula to return the 2nd best stat. There must be a way, either with this function or another similar function, to put in all the fields and put ", 2" at the end as I would with a max function.

Any suggestions?

Re: rangemax function - 2nd highest value

Tue, 13 Oct 2015 13:44:39 GMT

I am looking for such function too...Has there been a solution? Thanks!

Re: rangemax function - 2nd highest value

Tue, 13 Oct 2015 14:10:43 GMT

Didn't find a function that would do it, but I managed to get it to work by nesting rangemax functions. It works well for what I'm doing, taking the 2nd best of 4 stats, but if the nesting would increase exponentially with each additional stat. But it's great for 2-4 stats. I can send my expression if you'd find it helpful.

Re: rangemax function - 2nd highest value

Tue, 13 Oct 2015 14:18:33 GMT

Yes thank you very much! That would be very helpful! I have 4 expressions to compare as well.

Re: rangemax function - 2nd highest value

Tue, 13 Oct 2015 14:29:47 GMT

This is how I did it. For reference, TR, A, STL, BLK are all fields. ASSTS, REBS, STLS, BLKS are just text strings used to form a text string like "4 REBS, 3 ASSTS".

So basically the way it works is it looks to see if TR is the largest. If it is, then it ignores TR because I don't want the largest of the 4, I want the 2nd largest. So now that it knows TR is the largest, it finds the largest between the remaining 3 stats with nested rangemax and if/then functions. So that's the first section of the expression. The next section looks to see if A is the largest, and if it is, finds the largest of TR, STL, BLK. So it runs through all the possibilities. As I said, you could do this with more than 4 stats but each time you add one the expression grows exponentially larger. The full expression is below. Hope that's helpful!

if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=TR+.1,if(rangemax(A+.01,STL+.001,BLK+.0001)=A+.01,if(A=0,'',if(A=1,', ASST',', '&A&' ASSTS')),if(rangemax(A+.01,STL+.001,BLK+.0001)=STL+.001,if(STL=0,'',if(STL=1,', STL',', '&STL&' STLS')),if(BLK=0,'',if(BLK=1,', BLK',', '&BLK&' BLKS')))),

if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=A+.01,if(rangemax(TR+.1,STL+.001,BLK+.0001)=TR+.1,if(TR=0,'',if(TR=1,', REB',', '&TR&' REBS')),if(rangemax(TR+.1,STL+.001,BLK+.0001)=STL+.001,if(STL=0,'',if(STL=1,', STL',', '&STL&' STLS')),if(BLK=0,'',if(BLK=1,', BLK',', '&BLK&' BLKS')))),

if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=STL+.001,if(rangemax(TR+.1,A+.01,BLK+.0001)=TR+.1,if(TR=0,'',if(TR=1,', REB',', '&TR&' REBS')),if(rangemax(TR+.1,A+.01,BLK+.0001)=A+.01,if(A=0,'',if(A=1,', ASST',', '&A&' ASSTS')),if(BLK=0,'',if(BLK=1,', BLK',', '&BLK&' BLKS')))),

if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=BLK+.0001,if(rangemax(TR+.1,A+.01,STL+.001)=TR+.1,if(TR=0,'',if(TR=1,', REB',', '&TR&' REBS')),if(rangemax(TR+.1,A+.01,STL+.001)=A+.01,if(A=0,'',if(A=1,', ASST',', '&A&' ASSTS')),if(STL=0,'',if(STL=1,', STL',', '&STL&' STLS')))))

)))

Re: rangemax function - 2nd highest value

Tue, 13 Oct 2015 15:48:15 GMT

Thank you Brian, this is definitely helpful! Sunny T helped me in another thread and the ideas are similar. Max value of variables or an array‌

Thank you so much for your help!

Re: rangemax function - 2nd highest value

Maarsen — Tue, 04 Jan 2022 09:46:24 GMT

Hi,

I've solved it with RangeFractile. I had to check if the column is within the top 3. RangeFractile(0.5) gives you the median. The median with 7 colmns is the 4th number. But first/third quartil is possible as well. Is a bit easier formule.

If(A > RangeFractile(0.5, A, B, C, D, E, F), A, 0)