8 Replies Latest reply: Aug 2, 2018 8:03 AM by Anand kumar RSS

    Sum in Script

    Erica Trotter

      Hello,

       

      I am having trouble with this expression:

       

      Temp_Sum:

      Load

      [%CSLGranteeExternalID],

      [CSL Account Name],

      Sum(Num([CSL Total Annual Revenue])) as [CSL Sum Total Revenue]

      Resident Account

      Group By [%CSLGranteeExternalID],[CSL Account Name];

       

      It will not load, please let me know what you think could be the problem. I keep getting the error "Invalid Expression" when I run the script. 

        • Re: Sum in Script
          Chanty 4u

          did you tried without num and reload?

          • Re: Sum in Script
            Chanty 4u

            try

             

            Temp_Sum:

            NoConcatenate

            Load

            [%CSLGranteeExternalID],

            [CSL Account Name],

            Sum([CSL Total Annual Revenue]) as [CSL Sum Total Revenue]

            Resident Account

            Group By [%CSLGranteeExternalID],[CSL Account Name];

            • Re: Sum in Script
              Bala Bhaskar

              May be can you try like this:

              Account:

              Load

              [%CSLGranteeExternalID],

              [CSL Account Name],

              [CSL Total Annual Revenue],

              ...

              FROM ABC;

               

              NoConcatenate

               

              Temp_Sum:

              Load

              [%CSLGranteeExternalID],

              [CSL Account Name],

              Num(Sum([CSL Total Annual Revenue]),'##,#.00') as [CSL Sum Total Revenue]

              Resident Account

              Group By [%CSLGranteeExternalID];

               

              Drop table Account;

               

              You could check all the field names of Temp_Sum, matched correctly with Account table.

               

              Or else, could you provide some more script to identify the issue.

                • Re: Sum in Script
                  Erica Trotter

                  Hi, I actually ended up changing the script quite a bit, here is the new script I'm working on, but still haven't been able to get it working:


                  AllOrgs:

                  Load

                  "Grant ID",

                  %CSLGrantorExternalID,

                  [CSL Account ID],

                  [%CSLGranteeExternalID],

                  //[CSL Account Name],

                  if([CSL Rev by Funder]>20000,[CSL Account Name], 'All Other Grantees') as [CSL Account Name],

                  [CSL Account Type],

                  [CSL Zip],

                  [CSL Website],

                  [CSL Total Annual Revenue],

                  [CSL Total Annual Regranting],

                  [CSL Org Mission],

                  [CSL Year of Data],

                  [CSL No of Employees],

                  [RCF as Funder];

                  Load *,

                  Sum([CSL Total Annual Revenue]) as [CSL Rev by Funder]

                  Resident AllOrgs_TempGrantee

                  Group by %CSLGrantorExternalID;


                  Drop table AllOrgs_TempGrantee;

                    • Re: Sum in Script
                      Bala Bhaskar

                      Resident AllOrgs_TempGrantee


                      Where is this: AllOrgs_TempGrantee table?


                      May be you can try like this:


                      AllOrgs:

                      Load

                      "Grant ID",

                      %CSLGrantorExternalID,

                      [CSL Account ID],

                      [%CSLGranteeExternalID],

                      //[CSL Account Name],

                      if([CSL Rev by Funder]>20000,[CSL Account Name], 'All Other Grantees') as [CSL Account Name],

                      [CSL Account Type],

                      [CSL Zip],

                      [CSL Website],

                      [CSL Total Annual Revenue],

                      [CSL Total Annual Regranting],

                      [CSL Org Mission],

                      [CSL Year of Data],

                      [CSL No of Employees],

                      [RCF] as Funder;


                      NoConcatenate


                      AllOrgs_TempGrantee

                      Load *,

                      Sum([CSL Total Annual Revenue]) as [CSL Rev by Funder]

                      Resident AllOrgs

                      Group by %CSLGrantorExternalID,[CSL Account Type];


                      Drop table AllOrgs;


                      • Re: Sum in Script
                        Anand kumar

                        Hi Erica,

                         

                        Try this 3 and see if that helps:

                         

                        -try adding all fields in the group by except the aggregating field and then do a resident load of that in a different table

                        - Try converting the format of the measure in 1st table check in UI and then try aggregate it by resident load .

                        -try other aggregate functions with 1 field like max and continue adding other fields if it works then you can replace it by Sum

                         

                         

                        Anand