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

Remove rows base on conditions

HI, how to I remove rows base on conditions?

Group,name

A, Peter

A,Tan

A, Allen

B,Jacky

B,Tracy

B,Tom

I want to remove Group A because one of the name is Peter.

When I use    if(name <> Peter,1,0)  // I hides the 0's  , the other name in group A still shows. How do I remove them as well?

1 Solution

Accepted Solutions
trdandamudi
Master II
Master II

In your output if you want to eliminate the group where the name is 'Peter' then please use the attached file:

Your dimension will be as below:

=Aggr(Only({<Group -= P({<Name ={"Peter"}>}) >} Group),Group)

=Name

Hope this helps.....

View solution in original post

10 Replies
krishnacbe
Partner - Specialist III
Partner - Specialist III

Hi

Please find attached qvw. Hope that helps to resolve the issue.

trdandamudi
Master II
Master II

There are few ways you can resolve this, I used one of them and please go thru the attachment. Hope this helps...

RemoveRowsBasedOnCondtion.jpg

Anonymous
Not applicable
Author

HI Thirumala,

Your code seem to work if only one group contain name Peter. If the third group, C, contain Peter as well, it will not work. I have multiple groups with the same name.

I had attach back the same sample but included group C with name Peter in it.

trdandamudi
Master II
Master II

Please see the attached and hope this is what you want,,,,

trdandamudi
Master II
Master II

In your output if you want to eliminate the group where the name is 'Peter' then please use the attached file:

Your dimension will be as below:

=Aggr(Only({<Group -= P({<Name ={"Peter"}>}) >} Group),Group)

=Name

Hope this helps.....

Anonymous
Not applicable
Author

Thank You, it works!

Are you able to explain to me what the expression mean?

=Aggr(Only({<Group -= P({<Name ={"Peter"}>}) >} Group),Group)

trdandamudi
Master II
Master II

Glad to hear that it worked....

Explanation:

First I used Only() function to get the groups where the name is not 'Peter' :

Only({<Group -= P({<Name ={"Peter"}>}) >} Group)


Hope you noticed a hyphen '-' in front of the equal sign meaning not equal. In the other way I am saying bring all the groups expect the groups where the name is 'Peter'.

Then I am wrapping the only() function with Aggr() function to group it in the dimension like below:

=Aggr(Only({<Group -= P({<Name ={"Peter"}>}) >} Group),Group)


Hope this helps....



Anonymous
Not applicable
Author

I understand better now, is 'P' together with '-=' or does it have other meaning?

trdandamudi
Master II
Master II

The below expression is a Indirect Set Analysis (P means possible):

P({<Name ={"Peter"}>})

There are two types like below:

• p( ) = Possible

• e( ) = Excluded

This expression returns all possible records where Name= 'Peter'. Based on which record has the name Peter, then that is passed to the Group. Then we are eliminating that Group by using '- ='.

Hope this helps....