Join on multiple different senerios

[cowannbell]

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,

[chukleswk]

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

[cowannbell]

Maybe this will help.

See attachment

[cowannbell]

One thing I didn't mention, is that I already have a formula field in the table 2 flag table, that indicates what level that flag is at, i.e. Pin Only, TIN Only, Address Only, PIN & TIN, PIN & Address, PIN, TIN and Address and so on.

[chukleswk]

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

 

[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.

[chukleswk]

@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.