Searching for pairs in data

[Alex_Br]

Hello,

I am rather new to Alteryx so it might be, that my question has an easy solution, which I don't understand yet, but I have been looking through different threads here for a while and can't seem to find a solution to the problem I am facing in Alteryx. I have two Data Inputs and want to get all combinations defined in Input 2 which each user has in Input 1. Please see below the two inputs and the desired result.

Input 1

User	Function
User 1	Function X
User 2	Function X
User 2	Function Y
User 2	Function Z
User 3	Function Y
User 3	Function Z
User 4	Function X
User 5	Function X
User 5	Function Y

Input 2 

Risk	Function 1	Function 2
Risk 1	Function X	Function Y
Risk 2	Function Y	Function Z

Result:

User	Risk
User 2	Risk 1
User 2	Risk 2
User 3	Risk 2
User 5	Risk 1

So I want a list returning all Risks (which consist of 2 functions) which each user has. I have overall about 250 different risks I am looking for.

Thanks in advance for your help.

Alex

[atcodedog05 · Accepted solution]

Hi @Alex_Br 

I personally wouldn't say this is a simple straightforward usecase. I had to think couple of way to solve it problem 😅.

Here is how you can do it.

Workflow:

1. Using transpose tool to convert functions columns to rows.

2. Using join tool to join on function value.

3. Using crosstab to convert back to table with columns function_1 and function_2

4. Using filter tool to only keep rows where function_1 and function_2 are both not empty.

Hope this helps : )

[Alex_Br]

Thanks a lot for the quick reply. It seems to work great.

[atcodedog05]

Happy to help : ) @Alex_Br 

Cheers and have a nice day!