6 Replies Latest reply: Feb 24, 2015 4:31 PM by Peter Cammaert RSS

    How to Sum in Script

      I've created expressions in my app that give me the appropriate "buckets", but I need to export the data as a .txt, so I presume I need to do it in the script.  I've tried adding these lines to the script, but the load fails.

       

       

      Sum

       

      (if(CloseDate >= Now() AND CloseDate <= Now()+29, Total_SIMs__c)) as Funnel30,
      Sum(if(CloseDate >= Now()+30 AND CloseDate <= Now()+59, Total_SIMs__c)) as Funnel60,
      Sum(if(CloseDate >= Now()+60 AND CloseDate <= Now()+89, Total_SIMs__c)) as Funnel90,

       

       

      What is the best way to get thse sum categories into the final .qvd file?

       

      Thanks!  Dan

        • Re: How to Sum in Script
          Onno van Knotsenburg

          Try this:

           

          -----

           

          theTable:

          load

          categories,

          sum (if(CloseDate >= Now() AND CloseDate <= Now()+29, Total_SIMs__c)) as Funnel30,

          Sum(if(CloseDate >= Now()+30 AND CloseDate <= Now()+59, Total_SIMs__c)) as Funnel60,
          Sum(if(CloseDate >= Now()+60 AND CloseDate <= Now()+89, Total_SIMs__c)) as Funnel90,

          resident sourceTableName

          group by categories;

           

          store theTable into theTable.txt (txt);

           

          ----

           

           

          How does that look?

           

          • Re: How to Sum in Script
            Peter Cammaert

            You may have to change the parameter of the sum() function, but actually a similar technique is possible with the GROUP BY clause. A LOAD statement works row-by-row and doesn't allow aggregation functions without a GROUP BY clause that groups rows with similar characteristics.

             

            See the LOAD statement entry in QV Desktop Help.

            • Re: How to Sum in Script

              Thanks for the replies.  I should have mentioned that the LOAD statement is precdeing a SQL statement, so the load won't work because "categories" isn't in the SQL statement.  I also have a WHERE clause in the Load statement, so I presume the Group By would be after the Where.