5 Replies Latest reply: Aug 28, 2013 12:06 PM by Frank Beunder RSS

    Find highest Month and Value

    Frank Beunder

      Dear All,

       

      I have the following example data set:

      YearCaseMonthValue
      201339019112
      201339019122
      201339019133
      201339019143
      201339019154
      201339019164
      201339019175
      201339019186
      201343400212
      201343400224
      201343400231

       

      I want to create a table per year, that sums the values of the highest available month per Case.

       

      In this example for case 390191 the highest month is 8 with value 6 and case 434002 has highest month 3 with value 1.

       

      So the result should be:

      YearSum(value)
      20137

       

      How do I achief this??

       

      Thank you for your help!!

       

      Frank

        • Re: Find highest Month and Value
          Marcus Malinow

          Frank,

           

          just taken a look at this and

           

          1 - I've changed your load script to concatenate month and case into a new field MonthCase

           

           

          LOAD * INLINE [
              Year, Case, Month, Value
              2013, 390191, 1, 2
              2013, 390191, 2, 2
              2013, 390191, 3, 3
              2013, 390191, 4, 3
              2013, 390191, 5, 4
              2013, 390191, 6, 4
              2013, 390191, 7, 5
              2013, 390191, 8, 6
              2013, 434002, 1, 2
              2013, 434002, 2, 4
              2013, 434002, 3, 1
          ]
          ;

          Data:
          LOAD *,
          Num(Text(Month) & Text(Case)) as MonthCase
          RESIDENT TmpData;

          DROP TABLE TmpData;

           

          Then, I can display a table showing your Year dimension and this expression:

           

           

          Sum({$<MonthCase={$(=Concat(Aggr(Max(MonthCase),Year,Case),','))}>} Value)

           


          Hope this helps,

           

          Marcus

          • Re: Find highest Month and Value
            Celambarasan Adhimulam

            Try with firstsortedvalue

            =Sum(Aggr(FirstSortedValue(Value, -Month) ,Case))

            • Re: Find highest Month and Value
              Nicolas Gioux

              Hi Frank,

               

              To do what you describe, I think you should do it in 3 steps:

               

              1 - In the script, load in a temporary table the year, the case and the max month like this :

              TEMP:

              LOAD

                  Year,

                  Case,

                  Max(Month) as Month

              RESIDENT

                  DATA

              GROUP BY

                  Year, Case;

               

              2 - Always in the script, join the datas from the temporary table and a flag with your data table and drop the temporayr table like this :

              Left Join (DATA)

              LOAD

                  Year,

                  Case,

                  Month,

                  1 as [Flag max month]

              RESIDENT

                  TEMP;

               

              DROP TABLE TEMP;

               

              3 - The to sum the values of the highest available month per Case, use this expression :

              =Sum({$<[Flag max month] = {1}>} Value)

               

               

              I attach an application with this example.

               

              Hope it helps.

              Regards,

               

              Nicolas

              • Re: Find highest Month and Value

                Hi

                please find the attached file . hope will it helps you.

                 

                Thanks & Regards,

                Amuthabharathi.