Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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
bill_markham
Champion III
Champion III

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

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.