Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
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.
Indeed, GROUP BY comes after the WHERE clause.
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.
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.