Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: How to Sum in Script

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
Not applicable

Re: How to Sum in Script

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

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.

Peter_Cammaert
Not applicable

Re: How to Sum in Script

Indeed, GROUP BY comes after the WHERE clause.

oknotsen
Not applicable

Re: How to Sum in Script

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
Not applicable

Re: How to Sum in Script

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.