5 Replies Latest reply: Mar 5, 2018 2:55 PM by Sasidhar Parupudi RSS

    group by

    amber rebma

      how i use group by in script i use this but this is not working

       

      Directory;

      LOAD Acccount,

           SubAccounts

          

       

      FROM

      Book2.xlsx

      (ooxml, embedded labels, table is Sheet1)

      group by Account;

        • Re: group by
          Nagaian Krishnamoorthy

          Did you want to 'Order By' ?

          • Re: group by
            Pritam Basak

            To use group by in script you need at least a aggr function (SUM,AVG,COUNT)

            Directory;

            LOAD Acccount,

                 SubAccounts ,

                 Count(SubAccounts) as NoOfSubAccounts

            FROM

            Book2.xlsx

            (ooxml, embedded labels, table is Sheet1)

            group by Account;

             

            Without Aggregation function you will get an error like this

             

            Syntax error

             

            Aggregation expressions required by GROUP BY clause.

            • Re: group by
              Alejandro Wassermann

              Hi, You only need to use the Group by Clause if you are aggregating the information .

              For i.e :

               

              Directory;

              LOAD Acccount,

                  COUNT(SubAccounts) as QSubAccounts

                 

               

              FROM

              Book2.xlsx

              (ooxml, embedded labels, table is Sheet1)

              group by Account;

               

              That wouldn't give you a error message.

              If you express with some detail what you want to achieve we can help you out

              • Re: group by
                David Forest

                If you are just trying to weed out duplicates, you can use DISTINCT

                Directory;

                LOAD DISTINCT Acccount,

                     SubAccounts

                   

                 

                FROM

                Book2.xlsx

                (ooxml, embedded labels, table is Sheet1)

                ;

                • Re: group by
                  Sasidhar Parupudi

                  T1:

                  LOAD Acccount,

                       SubAccounts

                     

                   

                  FROM

                  Book2.xlsx

                  (ooxml, embedded labels, table is Sheet1)

                   

                  T2:

                  Noconcatenate Load

                  Account,

                  SubAccounts

                  Resident T1

                  Group By Account;

                   

                  Drop Table T1;