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

Filter on Sum Function in Script

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

Temp :

LOAD * INLINE [

    Dim, Fact

    A, 0

    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 ;

View solution in original post

3 Replies
Anonymous
Not applicable
Author

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

Temp :

LOAD * INLINE [

    Dim, Fact

    A, 0

    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 ;

agilos_mla
Partner - Creator III
Partner - Creator III

Hi,

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

Rgds,

Michael

Not applicable
Author

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