Issues with a Join

Good Morning!

I'm having a brain freeze trying to join some data. Essentially, I have a concatenate that I was using as a unique ID, but I found a handful of instances where my data in the concatenates can change from year to year. So, my join created additional rows for those instances, but I need them on a single row. My example is below, and I was wondering if anyone could think of a way I could join this outlier data. I thought about trying to sort and then have alteryx create a unique id and join on that, but I'm not sure what the best approaches would be. Could I use Fuzzy Match?

Data Stream 1 (Left Join)

Year	Client #	Name	Concatenate
2018	1	John Doe	1John Doe

Data Stream 2 (Right Join)

Year	Client # (Number has changed for X Reason, but the data is related to left join)	Name	Concatenate
2019	2	John Doe	2John Doe

Datasets

Accepted answers

danilang

hi @Rosaile

I'm not quite sure what the output of your join would be. Are you saying that the 2 records for John Doe should be joined into one row? I assume that joining on the just the name gives you too many matches.

How do you know that "1John Doe" and "2John Doe" are actually the same record? If you can quantify what additional information implies that result, you should be able add it to the join. If it's something like address, then maybe the Fuzzy Match tool can help you.

Dan

All comments

danilang

hi @Rosaile

Dan

Rosaile

Sorry, maybe this is a better example. Thinking of a single business paying property taxes to a specific county each year. That business may have its account number changed by the taxing jurisdiction from one year to another, and likewise, the business may change its own unique identifier from one year to another or the business may move addresses etc.

Either way, I have to keep track of the current year tax paid and the prior year tax paid, but it has to be reported on a single line both PY and CY. For most of my report, I can just create a unique ID through concats to satisfy this requirement, but instances where one of my fields used to create the concat change, I end up with more than one line.

Data Stream 1 (Left Join)

Year	Business Name	Business ID	Tax ID
2018	John's Business	123	456

Data Stream 2 (Right Join)

Year	Business Name	Business ID	Tax ID (Changed in New Year)
2019	John's Business	123	678


What I want to see on my report

Year	Business Name	Business ID	Tax ID (Changed in New Year)	Input #2 Year	Input #2 Business Name	Input #2 Business ID	Input #2 Tax ID (Changed in New Year)
2018	John's Business	123	456	2019	John's Business	123	678

Rosaile

Thanks for the assistance @danilang in getting me to think a little more on this.

I decided to try and "waterfall" the joins, if i'm using that term correctly, and join first by one of the fields, and then by next one...............until i pick up all the outliers.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703