Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Can you please advise me how can I get second or third max value in column range?
Col_1 | Col_2 | Col_3 | Col_4 | Col_5 |
10 | 20 | 30 | 40 | 50 |
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
I understand the requirement, In my test file it is working as expected? May be share sample instead of long rows
Hi Anil,
I have attached the qvw file, and removed all the unnecessary columns.
Please have a look.
Thanks
Farrukh
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..
Hi Farrukh,
Did you check my attachment? Have you got the expected result?
Hi Settu,
Sorry for late replying, Im adjusting the expression according to the requirement.
I will let you know tomorrow.
Regards,
Farrukh
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
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
Hi Farrukh,
Can you just remove equal sign ('=') in the variable and check it?
Attached your sample..
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
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