topic Re: Filter on Sum Function in Script in QlikView

Filter on Sum Function in Script

Fri, 02 Jan 2015 16:37:29 GMT

In loading my data, I am using the Sum() function to create a new field. It works fine but I also want to filter on that field in my script (I only want to pull where Sum()>0). I've tried a variety of things with a WHERE statement with no luck. Suggestions?

Re: Filter on Sum Function in Script

Anonymous — Fri, 02 Jan 2015 17:04:43 GMT

You could put the Where on a Preceding Load, something like the below :

Temp :

LOAD * INLINE [

Dim, Fact

A, 0

B, 1

B, 2

C, 3

C, 4

D, 5

D, 6

];

Data :

load

where SumFact > 0

;

load

Dim ,

Sum(Fact) as SumFact

resident Temp

Group By Dim

Order By Dim

;

Drop table Temp ;

Re: Filter on Sum Function in Script

agilos_mla — Fri, 02 Jan 2015 17:08:00 GMT

Hi,

I confirm Bill answer. Using a where clause in a preceeding load is equivalent to the SQL "Having" clause.

Rgds,

Michael

Re: Filter on Sum Function in Script

Fri, 02 Jan 2015 18:55:55 GMT

Thank you very much to both of you! I was putting my WHERE statement in my SQL code not the Preceding Load.