Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.....
Hi
Please find attached qvw. Hope that helps to resolve the issue.
There are few ways you can resolve this, I used one of them and please go thru the attachment. Hope this helps...
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.
Please see the attached and hope this is what you want,,,,
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.....
Thank You, it works!
Are you able to explain to me what the expression mean?
=Aggr(Only({<Group -= P({<Name ={"Peter"}>}) >} Group),Group)
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....
I understand better now, is 'P' together with '-=' or does it have other meaning?
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....