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
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,
Col_1, Col_2.. etc are Label of expressions, right? Then try like this..(for 3rd Max)
Pick(Match(
RangeMax(Col_1,Col_2,Col_3,Col_4,Col_5),
Col_1,Col_2,Col_3,Col_4,Col_5)-2,
Col_1,Col_2,Col_3,Col_4,Col_5)
I believe, Pick(Match(... Won't support expression like you said. I think we can create Key for all columns and we can use same?
Thread Owner - What is the output you want to get from given input?
Hi Settu,
It did not work
Farrukh
Hi Anil,
I have 5 columns where the numerical data exist.
Col_1 | Col_2 | Col_3 | Col_4 | Col_5 |
10 | 20 | 30 | 40 | 50 |
I want to pick nth value as per result, it could be first value , second value, or any from above 5 columns.
Kind regards,
Farrukh
Hi Anil,
I have written the formula in excel and its giving me correct result.
I am looking to write an expression in Qlikview.
Index Created: INDEX($BL3:$BP3
Max Value as per Input ((LARGE($BL3:$BP3,1)
And then Match
=IF(DN3>0,
IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,1)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"1H",
IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,2)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"2H",
IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,3)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"3H",
IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,4)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"2L",
IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,5)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"1L"))))),"")
Can you please advise me related to this? How can I achieve in Qlikvew.
Kind regards,
Farrukh
Please share Expected result set?
Hi Anil
Please see the attached excel sheet where the excel formula built-in.
Regards,
Farrukh
Hi Settu,
Please see the attached excel sheet where the excel formula built-in.
Can you please advise me how to get the similar result in excel.
Regards,
Farrukh
What you got, When you use this?
If(Sum(Seq_Count_1)>0,
If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),1)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '1H',
If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),2)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '2H',
If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),3)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '3H',
If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),4)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '2L',
If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),5)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '1L'))))), '')