26 Replies Latest reply: Feb 19, 2018 4:38 PM by Farrukh Shaikh RSS

    Second Max Value in Column Range (Straight Table)

    Farrukh Shaikh

      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

        • Re: Second Max Value in Column Range (Straight Table)
          Settu Periyasamy

          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)

            • Re: Second Max Value in Column Range (Straight Table)
              Anil Babu

              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?

                • Re: Second Max Value in Column Range (Straight Table)
                  Farrukh Shaikh

                  Hi Anil,

                   

                  I have 5 columns where the numerical data exist.

                  Col_1Col_2Col_3Col_4Col_5
                  1020304050

                   

                  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

                  • Re: Second Max Value in Column Range (Straight Table)
                    Farrukh Shaikh

                    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

                      • Re: Second Max Value in Column Range (Straight Table)
                        Anil Babu

                        Please share Expected result set?

                          • Re: Second Max Value in Column Range (Straight Table)
                            Farrukh Shaikh

                            Hi Anil

                             

                            Please see the attached excel sheet where the excel formula built-in.

                             

                            Regards,

                            Farrukh

                              • Re: Second Max Value in Column Range (Straight Table)
                                Anil Babu

                                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'))))), '')

                                  • Re: Second Max Value in Column Range (Straight Table)
                                    Farrukh Shaikh

                                    Hi Anil,

                                     

                                    The above expression did not work for me.

                                     

                                    I have attached the QVW and the (Example of Excel Sheet).

                                    Can you please advise me related to this?

                                     

                                    Kind regards,

                                    Farrukh

                                      • Re: Second Max Value in Column Range (Straight Table)
                                        Anil Babu

                                        Will test tomorrow, For a while can you set this? This will work until unless condition not fulfill

                                         

                                        If(Sum(CDC1)>0,

                                        If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),1)=Sum(CDC1), Sum(CDC1), 0),

                                        RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '1H',

                                        If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),2)=Sum(CDC1), Sum(CDC1), 0),

                                        RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '2H',

                                        If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),3)=Sum(CDC1), Sum(CDC1), 0),

                                        RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '3H',

                                        If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),4)=Sum(CDC1), Sum(CDC1), 0),

                                        RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '2L',

                                        If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),5)=Sum(CDC1), Sum(CDC1), 0),

                                        RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '1L'))))), '')

                                          • Re: Second Max Value in Column Range (Straight Table)
                                            Farrukh Shaikh

                                            Hi Anil,

                                             

                                            Hi have edited the above expression but still did not get luck.

                                             

                                            I highlighted below expression where I think I am having an issue. Please see the previously attached qvw file where I have but expression for CDF1,2.... and further.

                                             

                                            Please help me out for this. Kind regards, Farrukh

                                             

                                            =
                                            If(CDC1>0,

                                            If(IsNum(Index(RangeSum(
                                            Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
                                            ),
                                            Match(If(RangeMax(RangeSum(
                                            Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
                                            ),1)=
                                            Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), 0),

                                            RangeSum(
                                            Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
                                            Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
                                            ), 0)))=
                                            True(), '1H',0),0)

                              • Re: Second Max Value in Column Range (Straight Table)
                                Farrukh Shaikh

                                Hi Settu,

                                 

                                It did not work

                                 

                                Farrukh

                                • Re: Second Max Value in Column Range (Straight Table)
                                  Farrukh Shaikh

                                  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

                                • Re: Second Max Value in Column Range (Straight Table)
                                  Settu Periyasamy

                                  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..