Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

6 Replies
oknotsen
Master III
Master III

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?

May you live in interesting times!
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

Indeed, GROUP BY comes after the WHERE clause.

oknotsen
Master III
Master III

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.

May you live in interesting times!
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.