topic Re: How to Sum in Script in QlikView

How to Sum in Script

Tue, 24 Feb 2015 21:04:20 GMT

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

oknotsen — Tue, 24 Feb 2015 21:08:31 GMT

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 — Tue, 24 Feb 2015 21:10:19 GMT

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

Tue, 24 Feb 2015 21:22:24 GMT

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.

Re: How to Sum in Script

Peter_Cammaert — Tue, 24 Feb 2015 21:25:32 GMT

Indeed, GROUP BY comes after the WHERE clause.

Re: How to Sum in Script

oknotsen — Tue, 24 Feb 2015 21:26:03 GMT

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.

Re: How to Sum in Script

Peter_Cammaert — Tue, 24 Feb 2015 21:31:38 GMT

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.