Filter IDs if another Informationen is listed in a second database

[Bennet]

Hello,

second week of using alteryx starts with my second question.

I do have two databases that I need for this. One is the once with a lot of information about IDs and the second one is a database which needs to be compared and check if entries from the first database are known. The result will be a new table where all IDs are listed which have at one point the information from the second db.

ID	Information - Status change
1	A
1	B
2	C
2	DE1
2	B
2	DE2
2	C
3	A
3	DE1
3	D
4	A
4	C
4	D

second DB _ List

DE1

DE2

DE3

The IDs will be used more then once because the second column contains the status change of this ID. What I am looking for is a solution that will afterwards list all IDs where information was found in the second database. That means if at one point the second column is the same as in the second db all entries with this ID should be filtered to a new list. In my example the result should contain all lines with ID 2 and 3. Only using the join tool didnt work for me.

I hope this is understandable. If not please let me know and thank you for your time.

Br Bennet

[jdunkerley79]

The join tool will allow you to do what you need. 

https://help.alteryx.com/11.3/Join.htm

The L and R output contains records that were not joined so this should let you get what you need

[Bennet]

Hi,

not really. What I am searching for is the option that all Ids that had at one point a match with the other list will be shown. your solution will only bring out the IDs where the match was. The problem here is that I will loose the lines where other information concerning this ID is provided.

As a result I will need all one list where all the bold written lines are shown.

Br Bennet

[jdunkerley79 · Accepted solution]

Sorry had misread - you need to do two stages of join I think.

First join to get ones where in both

Then make a unique ID list

Then join back to original set to get all records

 

I think that is what you are asking for

[Bennet]

Hello,

create this is totally working for me. Havn't worked with the Unique tool so fare. This is now added to my "Needed tool list". :)

This was really helpful thanks so much for your support.

Br Bennet