4 Replies Latest reply: Nov 21, 2016 12:00 PM by David Bockol RSS

    Count and Group by Clause

    David Bockol

      I created a resident table called T5_Retention.  I'm trying to count the number of accounts in each unique grouping using the following script.  The script does not work.  How do I correct this script ?

       

      The expected result should look as shown below.  I attached the qvw document and supporting data.

       

        • Re: Count and Group by Clause
          Marco Wedel

          a more dynamic solution might be to count in a chart expression instead of the script.

           

          hope this helps

           

          regards

           

          Marco

          • Re: Count and Group by Clause
            Fer Fer

            Hi David,

            T5_Retention:
            LOAD *, 1 AS Policies;
            LOAD
            * Inline
            [
            Account No,     Code, Entity_Status2, Studflag,  Profession,     Yrsins3,   Base Premium
            615560545, APROFCON23,     A,   PROF, CON, 23,  669
            617485102, APROFNEW1, A,   PROF, NEW, 1,   500
            597388733, APROFNEW23,     A,   PROF, NEW, 23,  833
            615091182, APROFNEW23,     A,   PROF, NEW, 23,  846
            615238701, APROFNEW23,     A,   PROF, NEW, 23,  1028
            246554344, APROFNEW99,     A,   PROF, NEW, 99,  192
            298933256, APROFNEW99,     A,   PROF, NEW, 99,  600
            415389121, APROFNEW99,     A,   PROF, NEW, 99,  625
            256038801, APROFNEW99,     A,   PROF, NEW, 99,  1349
            415354630, APROFNEW99,     A,   PROF, NEW, 99,  2701
            619225814, TPROFNEW1, T,   PROF, NEW, 1,   846
            615570346, TPROFNEW23,     T,   PROF, NEW, 23,  600
            274411835, TPROFNEW99,     T,   PROF, NEW, 99,  814
            273466366, TPROFPHY99,     T,   PROF, PHY, 99,  1237
            273038314, TPROFPHY99,     T,   PROF, PHY, 99,  1988
            246961001, TPROFPPM99,     T,   PROF, PPM, 99,  649
            255892592, TPROFPPM99,     T,   PROF, PPM, 99,  796
            252198117, TPROFPPM99,     T,   PROF, PPM, 99,  873
            256061317, TPROFPPM99,     T,   PROF, PPM, 99,  916
            265678272, TPROFPPM99,     T,   PROF, PPM, 99,  2404
            ]
            ;


            VARIOUS.png

            • Re: Count and Group by Clause
              Anna Pochyla

              Hi,

              you should list the fields instead of using '*'.

              '*' contains also [Customer No] field, which you ommited in Group by clause. And this is cousing an error.

               

               

              LOAD Code,Entity_Status2,Studflag,Profession,Yrsins3,Count ([Customer No]) as Policies

              Resident T5_Retention

              Group by Code,Entity_Status2,Studflag,Profession,Yrsins3;