6 Replies Latest reply: May 4, 2012 6:15 PM by Benoit ARMBRUSTER RSS

    Max Group By 2 fields

    Benoit ARMBRUSTER

      Hi,

       

      I try to use the max() function with a Group By and 2 values.

       

      My DATA are looking like this :

       

       

      QuarterType_of_ValueValueCountry
      Q1A230UK
      Q1A200US
      Q1A250FR
      Q1B120UK
      Q1B130US
      Q1B100FR
      Q2A300IT
      Q2A250US
      Q2B100FR
      Q2B110UK
      Q2........

       

       

      I need to achieve the following table :

       

       

      Quarter MaxTypeofValueMaxCountryValueMaxCountry
      Q1A250FR
      Q1B130US
      Q2A300IT
      Q2B110UK
      ...


       

       

      I have tried with the following script :

       

      DATA_MAX:

      LOAD

      max(Value) as MaxCountryValue,

      Type_of_Value as Max_Type_of_Value,

      Quarter

      FROM

      DATA_SOURCE.qvd

      (qvd)

      Group by Quarter,Type_of_Value;

       

      Left Join

       

      LOAD Country as MaxCountry,

           Value as MaxCountryValue

      FROM

      DATA_SOURCE.qvd

      (qvd);

       

      But I obtain this wrong result :

       

      QuarterMaxTypeofValueMaxCountryValueMaxCountry
      Q2A300IT
      Q1B130US

       

       

      All idea or remark is welcome !

      Thanks a lot !

       

      Benoit

        • Re: Max Group By 2 fields

          Have you tried reversing the group by? Not sure if it will work but worth a try:

           

          Group by Type_of_Value,Quarter;

           

           

           

          You could also try a left keep?

          • Re: Max Group By 2 fields
            Brandon Apperson

            I have this script and the results come out fine:

             

            Table1:

            LOAD * INLINE [

            Quarter, Type_Of_Value, Value, Country

            Q1, A, 230, UK

            Q1, A, 200, US

            Q1, A, 250, FR

            Q1, B, 120, UK

            Q1, B, 130, US

            Q1, B, 100, FR

            Q2, A, 300, IT

            Q2, A, 250, US

            Q2, B, 100, FR

            Q2, B, 110, UK

            ];

             

             

             

             

             

             

            Table2:

            LOAD Quarter,

            MAX(Value) as MaxCountryValue,

            Type_Of_Value as MaxTypeOfValue

            FROM

            C:\Users\BApperson\Desktop\Table1.qvd

            (qvd)

            GROUP BY Quarter, Type_Of_Value;

             

             

            LEFT JOIN

             

             

            LOAD

            Quarter,

            Value as MaxCountryValue,

            Country as MaxCountry

            FROM

            C:\Users\BApperson\Desktop\Table1.qvd

            (qvd);

             

             

            drop table Table1;

             

            Part of what might be a problem is what you are joining on, notice in the second statement I have Quarter and MaxCountryValue that are being joined on.

             

            Hope this helps.

             

            -Brandon

            • Re: Max Group By 2 fields
              Benoit ARMBRUSTER

              I tried your two solution ... finally I find why my solution was wrong : my table wasn't sorted ...

              Thanks a lot for your help !

                • Re: Max Group By 2 fields
                  Benoit ARMBRUSTER

                  Well,

                   

                  In fact, my goal is to obtain a table and a list of selection on the quarter. If I choose Quarter Q1 it should display this table :

                   

                   


                  COUNTRY
                  Type AFR

                  250
                  Type BUS

                  130

                   

                   

                  Has anyone an idea to obtain this result without using Firstsortedvalue in the table (it takes too many time to display dynamically).

                   

                  Thanks again for your help !

                    • Re: Max Group By 2 fields

                      Hey, I'm not sure what do you mean by "without using FirstsortedValue".  Try the below script. Should work for your case. It uses an aggregation function. 

                       

                      Table1:

                      LOAD * INLINE [

                      Quarter, Type_Of_Value, Value, Country

                      Q1, A, 230, UK

                      Q1, A, 200, US

                      Q1, A, 250, FR

                      Q1, B, 120, UK

                      Q1, B, 130, US

                      Q1, B, 100, FR

                      Q2, A, 300, IT

                      Q2, A, 250, US

                      Q2, B, 100, FR

                      Q2, B, 110, UK

                      ];

                      load
                      Quarter,
                      Type_Of_Value,
                      max(Value),
                      lastValue(Country)
                      resident Table1 group by Quarter,Type_Of_Value order by Value;

                      drop table Table1;

                       

                      Regards,

                      Xue Bin

                  • Re: Max Group By 2 fields
                    Benoit ARMBRUSTER

                    Thx Xue Bin !

                    It's working perfectly !

                     

                    Regards,

                     

                    Benoît