Remove rows base on conditions

[Anonymous]

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?

[krishnacbe]

Hi

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

[trdandamudi]

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

[Anonymous]

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]

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

[trdandamudi · Accepted solution]

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]

Thank You, it works!

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

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

[trdandamudi]

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]

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

[trdandamudi]

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....