How to Sum in Script

[#1]

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

[oknotsen]

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?

[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.

[#4]

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.

[Peter_Cammaert]

Indeed, GROUP BY comes after the WHERE clause.

[oknotsen]

So where is the categories coming from? Or am I misunderstand your data structure?

My script was assuming you have the table loaded already, categories is a field in it and you are summing some other field. I do a resident load on that table.

[Peter_Cammaert]

Nevermind the sequence of LOAD/SELECT statements. Add one or more grouping field to both the SELECT and PRECEDING LOAD. IF you want to Sum() and not end up with a single result row, you should add a grouping field.

Otherwise, drop the GROUP BY/SUM logic and add three FunnelXX fields to every row. For each individual row, you will only fill one of them.