Help required on filter/join

[Chelseaa]

Hi all! I would really appreciate some help on this:

ID	Name	Project
1	Mary	A
1	Sam	B
1	Danny	D
2	Tom	A
2	Anna	A
3	Polina	C
4	Nick	D

I would like to filter out all the Project As, however, each person is being grouped under an ID and if anyone in that ID is in Project A, i would like to have all of them under that ID to be filtered out as long as someone in that ID is under project A.

My desired output is:

ID	Name	Project
1	Mary	A
1	Sam	B
1	Danny	D
2	Tom	A
2	Anna	A

I have tried to first filter out those in Project A, then use a Join tool to get the rest of the people out under the same ID but it is not working and there are missing output from my L, J and R

Please help! Thanks alot in advance 🙂

[AngelosPachis · Accepted solution]

Hi @Chelseaa ,

I've mocked up a workflow that returns the output you were asking for. I used a summarise tool to concatenate all projects in each ID, and then I filtered those not containing an "A" before joining to your main data.

So out of your J output anchor you have your desired output and out of the R output anchor the ID that don't have an "A" as a project.

Hope that helps.

Regards,

Angelos

[jdunkerley79 · Accepted solution]

This should do what you want:

- First filter to just Project A

- Get the unique IDs (I used a summarise to do this)

- A Join tool then can be used to filter down to just those projects

Sample attached

[atcodedog05 · Accepted solution]

Hi @Chelseaa 

Here is a workflow for the task.

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍