11 Replies Latest reply on Mar 8, 2018 7:04 AM by ir_nerd

    How to find second highest value in array

    lbuurma

      Is there a non-custom assembly way to retrieve the second highest value in an aggregate? I can of course retrieve the Max(my aggregate)  - and save it in a value - but there doesn't appear to be a way to use  that value in a conditional expression or filter based on it.  Is there (as usual) a clever alternative I'm missing short of writing my own function?

       

      (RTE editor, A.04.07 RZChrom is specific platform...)

       

      Regards

       

      Lisa

        • Re: How to find second highest value in array
          ir_nerd

          Hi Lisa,

           

          Let me have a look on that. I will get back to you ASAP.

           

          Regards,

          Pierre

            • Re: How to find second highest value in array
              lbuurma

              Thanks Pierre

               

              In the meantime sorted my table with descending values of the target field (Peak_AreaPercent) and I did this little Custom assembly to retrieve the desired value. Works fine but would love to eliminate any custom code dependencies…

               

              Lisa

               

               


              Dim SortedArray(0) As Double
              Public Function AddValue(ByVal VarValue As Double) As String
              Try
              If IsNothing(SortedArray(0)) Then
              SortedArray(0) = VarValue
              Else
              ' not there so add it
              ReDim Preserve SortedArray(UBound(SortedArray) + 1)
              SortedArray(UBound(SortedArray)) = VarValue
              End If

              Return "Successful"

              Catch ex As Exception
              Return ex.Message
              End Try

              End Function



              Function GetSortedValue(ByVal ValIndex As Integer) As Double
              Dim nLoop As Integer
              Try
              Return SortedArray(ValIndex)
              Catch ex As Exception
              Return Nothing
              End Try
              End Function

                • Re: How to find second highest value in array
                  ir_nerd

                  Hi Lisa,

                   

                  Thank you for the custom assembly! This might be very helpful.

                  Saving the value of a nBiggest value in array is possible. You can then return this value as you wish in any field or table.

                  However, using this saved nBiggest value in a group or filter expression will not be possible due some limitation that the Microsoft renderer has (in Intelligent Reporting for OpenLAB ChemStation and EZChrom, we use this renderer to generate the report). This is why having this value in a custom assembly may be very useful. That limitation is addressed in OpenLAB CDS 2.x because, we have other calculation possibilities.

                   

                   

                  To come back to your request, and how to save the nBiggest value in an aggregator :

                   

                   

                       1. Create a table without group repeat criteria, but with a descending order sorting option on the array that you want to work with :

                           

                           Peak area if you want to save the "n" biggest peak area in array

                   

                       2. In this table, place at least the 3 following columns : RT, A column called "Count", and the peak area in the 3rd :

                      

                   

                       3. Go to the column "RT [min]" properties, and then in the value tab, click "fx" to go in the expression editor

                   

                       4. In this expression editor, click "Save Expression Result As", an then save this expression as an Aggregator called for example "Count1", and click "save", "ok" to quit the exp. editor.

                   

                       5. Go to the column "Count" properties, and in the value, retrieve the count function of the aggregator that you just created. Expression should be something like "=Count(Count1)"

                       6. Go to the Peak_Area column, and write this expression : "=IIF(Count(Count1)=2,Round(Peak_Area,3),0)" which returns the 2nd peak area of the aggregator, or 0 in the case the peak area is not the second.

                   

                       7. In this Expression editor, click "Save Expression Result As", and save this expression as an Aggregator called "NBiggestArea" for example.

                   

                       8. Add under the table, a text field having the expression "=Max(NBiggestArea)"

                   

                       9. If you have several detector select both table and text field, and group these 2 items. In the group properties, select group repeat by "Signal_Name"

                      

                   

                       10. You can add, other text field to have the signal name in the composite group, as well as any cosmetic things if you want

                   

                       11. If you just use this table to save the value, you can also hide the entire composite group, by goind in the advanced group properties, and uncheck the checkbox visible

                           

                       12. You can also have the value "n" taken from a custom report parameter (you can change the value of the "n" desired peak area when manually generating your report) or a custom field (to change the "n" desired value from the acquisition").

                   

                   

                  Once this group hidden, if you need the "n" biggest peak area value, just recall "=Max(NBiggestArea)"

                  1 person found this helpful
                    • Re: How to find second highest value in array
                      lbuurma

                      Well, aren’t you clever!!!     I stared at this for an hour and never came up with that workaround!  Thank you so much!In this case I think your little dance will do exactly what they need and remove the dependency… I’ll check it out!

                       

                       

                       

                      Happy to provide silly little custom assemblies any time you want..  

                       

                      Regards

                       

                      Lisa

                • Re: How to find second highest value in array
                  rhonsberg

                  Hi lbuurma,

                  thnx for you contributions! Could you set your question to answered!?

                   

                  regards

                  Ralf Honsberg

                  • Re: How to find second highest value in array
                    ryoboyle

                    I updated the tags on this thread to make it easier to search.

                     

                    Best Regards,

                    Ryan

                    • Re: How to find second highest value in array
                      petur

                      Hi

                      How can I show only the 30 biggest peaks in the table based on the "area %" value ?