topic Re: Remove rows base on conditions in QlikView

Remove rows base on conditions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Remove rows base on conditions

krishnacbe — Mon, 24 Oct 2016 13:55:50 GMT

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

Re: Remove rows base on conditions

trdandamudi — Mon, 24 Oct 2016 14:25:29 GMT

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

Re: Remove rows base on conditions

Anonymous — Tue, 25 Oct 2016 01:27:32 GMT

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.

Re: Remove rows base on conditions

trdandamudi — Tue, 25 Oct 2016 13:31:02 GMT

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

Re: Remove rows base on conditions

trdandamudi — Tue, 25 Oct 2016 15:19:24 GMT

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

Re: Remove rows base on conditions

Anonymous — Wed, 26 Oct 2016 02:17:15 GMT

Thank You, it works!

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

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

Re: Remove rows base on conditions

trdandamudi — Wed, 26 Oct 2016 06:56:51 GMT

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

Re: Remove rows base on conditions

Anonymous — Wed, 26 Oct 2016 07:02:23 GMT

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

Re: Remove rows base on conditions

trdandamudi — Wed, 26 Oct 2016 16:01:01 GMT

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

Re: Remove rows base on conditions

Anonymous — Thu, 27 Oct 2016 00:56:41 GMT

Alright! I understand. thks!