N to N matching (1 to N loop ?)

[SaoriYamguchi]

Hi, I use Alteryx every day 

So,I want to match the two table.

Table A

USERID request1  request2  request3

U1            AA           12             aaa

U1            BB           23             bbb

U2            AA           Null           ccc

U3　   　 Null         22             aaa

TableB

ITEMID  request1  request2  request3

IT1           AA           23            ccc

IT1          AA           22             ccc

IT2         AA             12            aaa

IT2         BB            22             aaa

IT3         CC           12            aaa

TableC （matching result）

USERID ITEMID

U3　      IT3

U1         IT2  

So,TableA and TableB's request name are  same meaning.

And, ITEMID and USERID's want condition are multiple.

And,ITEMID and USERID's want condition are "Null" that means "Wildcard"(All matching)

I want to resolve the problem.

But,This problem is too compex.

I think that I use macro, but this problem is very complexity.

Thank you for your kindness.

　

[BenMoss]

It's difficult to understand exactly what you are looking for.

Based on the sample data you provided us, could you build a fourth table to show what the output would look lik3.

Ben

[SaoriYamguchi]

Thank you ,I want to make Table 3 

[SaoriYamguchi]

Sorry, I want to make tableC

TableA and TableB match after make TableC

Thank you

[mceleavey]

I think I understand the requirement, but I don't understand why in Table C you have U3 linked to IT3. That doesn't appear to be correct.

[SaoriYamguchi]

Thank you.

Sorry That is My mistake。

I miss the TableA and TableB's request2  are 22 only.

That is not match.

Thank you.

[mceleavey]

Hi @SaoriYamguchi,

Isn't it a simply case of matching across multiple fields?

If you join on all request fields like so:

You get the result:

[SaoriYamguchi]

Thank you!!
But, UserID data have null that means wildcard.
I am confusing.

[mceleavey · Accepted solution]

Hi @SaoriYamguchi,

The UserID is not being used in the join, so it wouldn't matter if it was populated or not.

The joins would be made on the combination of the requests only.

[SaoriYamguchi · Accepted solution]

Thank you！