Join on multiple different senerios

I have a list that I need to join to another list but based on multiple different scenarios.

The first list will have three fields, Pin, TIN and Address

The second list will have the same fields but some might be blank.

For example The second list has one line with the Pin but Tin and address are blank. It has a second line that matches on Pin and Address but the TIN is blank, a third line that matches on address only and a 4th line that match on all three. On the second list, there is a column called flag. What I need to do is pull the flag into the first line but only based on the match of that line. So if there are 2 lines with different flags but only on the Pin, then I need to pull those two. If there are 3 lines with flags that match on PIN and TIN, then I need to pull those also but match on PIN and TIN, etc.

Hoping someone has some ideas.

Thanks,

Connectors

Accepted answers

All comments

chuckleswk

@cowannbell It would be helpful if you have sample files that we could look at.

cowannbell

Maybe this will help.

See attachment

alteryx.png

chuckleswk

I am attaching what I was able to build that would work.

Join on Multiple.PNG

Multiple Join.yxmd

cowannbell

I have tried this and I believe it would work but It won't work for what I'm doing because it takes to long and to much temp space to run. I think it's just the amount of data that I'm working with.

chuckleswk

@cowannbell if you have alteryx server, you may try running it in that as it should have a larger memory than what you are currently running. The other thing you could do is break it down in chunks and run portions of the data through a macro compiling it all at the end back together. Out of curiosity, how many rows of data are you trying to join together?

cowannbell

I actually finally got it to work but it's not pulling what I need.

The match on field doesn't work in my situation. So the match field comes like this. Address-0,Pin-0,Tin-123456789, so it won't match my flag table because the flag table match field is Address-1234,Pin-0,Tin-123456789

I need it to match even if only one of the fields matches.

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157