I have a table like this one
ID Letter
1 A
1 A
1 B
2 A
2 A
2 A
3 A
3 B
3 B
What I need to do is remove all rows from the IDs if only one of the lines of that ID contains the value B.
In this exemple, the result should be
2 A
2 A
2 A
How exactly can I do this? Thanks in advance
Quote from: daniel1409z on 03 Apr 2023 01:14:52 PM
I have a table like this one
ID Letter
1 A
1 A
1 B
2 A
2 A
2 A
3 A
3 B
3 B
What I need to do is remove all rows from the IDs if only one of the lines of that ID contains the value B.
In this exemple, the result should be
2 A
2 A
2 A
How exactly can I do this? Thanks in advance
Hi,
I'm not quite clear on the exact requirement - the description you wrote doesn't seem to match the data you posted?
ID 1 has one row containing B - so that should be removed
ID 2 has no rows containing B, so that should be displayed
ID 3 has two rows containing B - should it be removed or displayed?
Your description said
remove all rows from the IDs if only one of the lines of that ID contains the value B, so if that's true, ID 3 should be displayed? In the results table it isn't though?
If ID 3 should be removed, then a simple filter maximum([Letter] for [ID]) <> 'B' should do the trick
If ID 3 should be displayed, then the filter should be maximum([Letter] for [ID]) <> 'B' or (maximum([Letter] for [ID]) = 'B' and count([Letter] for [ID]) > 1)
Cheers!
MF.
Hi, thanks for the answer!
Yep, it was not clear indeed. In this case, I want to remove the ID 3 as well, sorry!
"If ID 3 should be removed, then a simple filter [Letter] <> 'B' should do the trick"
Correct me if I'm wrong, but if I include this filter, the IDs 1 and 3 will stay on the report, right? It will remove only the rows with the B but the rows with A will stay.
Quote from: daniel1409z on 03 Apr 2023 02:05:24 PM
Hi, thanks for the answer!
Yep, it was not clear indeed. In this case, I want to remove the ID 3 as well, sorry!
"If ID 3 should be removed, then a simple filter [Letter] <> 'B' should do the trick"
Correct me if I'm wrong, but if I include this filter, the IDs 1 and 3 will stay on the report, right? It will remove only the rows with the B but the rows with A will stay.
Yes - apologies. I realized my mistake immediately after posting and amended the post. It now reads:
If ID 3 should be displayed, then the filter should be maximum([Letter] for [ID]) <> 'B' or (maximum([Letter] for [ID]) = 'B' and count([Letter] for [ID]) > 1)
Does that work?
Cheers!
MF.
Yep, the maximum filter worked. Thank you so much!!!