15 Replies Latest reply: Jan 22, 2013 12:40 PM by Oleg Bragin RSS

    firstSortedValue NULL

      Hi there,

       

      I'm using firstSortedValue but it doesn't work because some of my data has the same "order" and so it returns me null.

      Is there a way to "emulate" firstSortedValue with data havaing the same order ? So instead of returning null it'll return the first result of the set.

       

      Thx.

        • firstSortedValue NULL
          Celambarasan Adhimulam

          Hi,

               Use MinString or MaxString in that case

          MaxString({<Date={"$(Max(Date))"}>} Field)

           

           

          Celambarasan

          • Re: firstSortedValue NULL
            Rob Wunderlich

            It depends on your rule for selecting from the duplicate sort values. You can add the DISTINCT keyword and you'll get the first value by load order. For example:

             

            Date, Value

            1/1, 100

            1/1, 150

             

            =firstsortedvalue(DISTINCT Value, Date)

            will return 100.

             

            If you have another rule, such as highest/lowest value, don't use DISTINCT but modify sort value with a bias of the Value itself.

            =firstsortedvalue(Value, Date+(Value/1000000))

            will return 150.

             

            Note tha the bias does not have to come from the Value -- it could be calculated on any other connected field.

             

            -Rob

            http://robwunderlich.com

            • Re: firstSortedValue NULL
              Oleg Bragin

              Hello everyone.

              Another question about FirstSortedValue:

              What if the first parameter in FirstSortedValue function is string? so we can't use (Value / 1000000) as types are not compatible?

               

              Globaly the problem is that we (my project) will definitely have duplicate records which we need to sort. And everytime FirstSortedValue sees duplicate it returns NULL. If we put DISTINCT keyword in this function we'll get a reduced number of records what is not acceptable. Is there a way to implement sorting in expressions without this function? or in a completely different way?

               

              Thanks

                • Re: firstSortedValue NULL
                  Rob Wunderlich

                  Can you post some sample data and your desired result?

                   

                  -Rob

                    • Re: firstSortedValue NULL
                      Oleg Bragin

                      For instance we have 3 students Aaa, Bbb, Ccc. Aaa has 5 Scores, Bbb and Ccc have 4. In table view:

                       

                      Student Score

                      Aaa       5

                      Bbb       4

                      Ccc       4

                       

                      If we don't use DISTINCT we get empt.

                      If we use FirstSortedValue with DISTINCT we get the following results:

                      Student Score

                      Bbb      4

                      Aaa      5

                       

                      So a student Ccc is gone, as he has the same Score as Bbb and we only take distinct here.

                       

                      And we want to have all the records sorted as usual and also have all the original records in place.

                      So like this:

                       

                      Student Score

                      Ccc       4

                      Bbb       4

                      Aaa       5

                       

                      Alphabet sorting is irrelevant now, we just need to keep the identical records in place.