Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Second Max Value in Column Range (Straight Table)

Hi Guys,

Can you please advise me how can I get second or third max value in column range?

   

Col_1Col_2Col_3Col_4Col_5
1020304050

Currently I am using RangeMax & RangeMin to get Max or Min value. But I get to get 2 or 3 from the range.

Kind regards,

Farrukh

32 Replies
Anil_Babu_Samineni

I understand the requirement, In my test file it is working as expected? May be share sample instead of long rows

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Hi Anil,

I have attached the qvw file, and removed all the unnecessary columns.

Please have a look.

Thanks

Farrukh

settu_periasamy
Master III
Master III

Hi Farruukh,

Can you check the attachment? is that you needed?

due to large expression (may be there is a easy way), i have created variables like

v1H - 1st Higher

v2H - 2nd Higher

v3H - 3rd Higher

v1L - 1st Low

v2L - 2nd Low

v3H: (To find the 3rd maximum based on column label)

=rangemax(
IF(RangeMax(
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF1,

RangeMax(
If(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
If(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
IF(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5) ),

IF(RangeMax(
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF2,

RangeMax(
If(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF1, 0, CDF1), 
If(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
IF(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF1, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5)),

IF(RangeMax(
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF3,

RangeMax(
If(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF1, 0, CDF1), 
If(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF2, 0, CDF2),
IF(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF1, CDF2, CDF4,CDF5) = CDF5, 0 ,CDF5)),

IF(RangeMax(
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF4,

RangeMax(
If(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF1, 0, CDF1), 
If(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF2, 0, CDF2),
IF(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF3, 0 ,CDF3),
IF(RangeMax(CDF1, CDF2, CDF3, CDF5) = CDF5, 0 ,CDF5)),

IF(RangeMax(
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF1, 0, CDF1),
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF2, 0, CDF2), 
If(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF3, 0, CDF3),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF4, 0 ,CDF4),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4,CDF5) = CDF5, 0 ,CDF5))=CDF5,

RangeMax(
If(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF1, 0, CDF1), 
If(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF2, 0, CDF2),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF3, 0 ,CDF3),
IF(RangeMax(CDF1, CDF2, CDF3, CDF4) = CDF4, 0 ,CDF4))

) ) ) ) ) )

Select_F1 Expression :

if("CDC1">0,
Pick(
Match(CDF1,$(v1H),$(v2H),$(v3H),$(v1L),$(v2L)),
'1H','2H','3H','1L','2L'))



Like wise other expression..



settu_periasamy
Master III
Master III

Hi Farrukh,

Did you check my attachment? Have you got the expected result?

Anonymous
Not applicable
Author

Hi Settu,

Sorry for late replying, Im adjusting the expression according to the requirement.

I will let you know tomorrow.

Regards,

Farrukh

Anonymous
Not applicable
Author

Hi Settu,

Thanks a mil. Its sorted. Sorry to keep you waiting.

Just for a knowledge is there any function like excel has (Large(Range, Rank)) in Qlikvew. By this function it can give the nth Max or Min as per the requirement.

Kind regards,

Farrukh

Anonymous
Not applicable
Author

Hi Settu,

The expression works for first five columns, but when I created the variables for other columns counts then its not working.

Please see the attached sample table.

What I am trying to do is looking for (1H, 2H, 3H, 1L, 2L) in each block.

Block mean is

(CDC1 to CDC5)

(CDC6 to CDC10)

(CDC11 to CDC15)

When I create the variable for block-2 (CDC6 to CDC10) then its not showing the value for Block-1.

Please advise me how this can be sorted.

Kind regards,

Farrukh

settu_periasamy
Master III
Master III

Hi Farrukh,

Can you just remove equal sign ('=') in the variable and check it?

Attached your sample..

Anonymous
Not applicable
Author

Hi Settu,

Thanks for coming back to this.

Settu, left table from below snapshot some records are missing. I just put the another table which is presenting the value in the cell but its not in right table.

Also 3rd Max is not giving the correct value as per the cumulative count.

Kindly have a look at this.

I have attached a qvw file.

Farrukh

Anonymous
Not applicable
Author

Hi Settu,

Just want to let you know that now its giving me the correct result as you advise previously. and I implement the same expression as you provided.

YOU ARE SUPPER....

thanks for your great help.

Farrukh