Join multiple fields/conditions including non-exact (potentially using find and replace)

[VanessaB]

Hi everyone, 

Complete newbie here but loving the use of Alteryx so far. I have what seemed as a simple enough scenario but I've done plenty of digging and haven't seen a relevant answer for yet. 

• So I was using the join tool which worked well until there were scenarios where I didn't want the exact join but a part of a string (CONTAINS "FU" instead of EQUALS "FU") - for this, I used find and replace tool.
• The problem is that the original join was on multiple specific fields, which I can't seem to duplicate with any other combination of tools

Data set:

'Joining/mapping' file:

So ordinarily I'd use the join tool to match up the first two columns to my data set. No problem until I get to the 3rd field whereby the data in the data set only contains and not completely match.

I've highlighted the first row as an example. So I want to

• Join field 1 >> Category of mapping file (Both CASH)
• Join field 2 >> Subtype of mapping file (Both CURRENT)
• Field 3 contains FU (45FU4385) >> IDer of mapping file
• So the field that should append or join up should be "Future cash"

But if I use find and replace, it will variably be "Future Margin Account" because it isn't matched up to the previous fields and only the third one:

In the past, I've just accepted that the easiest is to a join and then manually assign formulae to the ones that don't match up exactly. But at this point, I'm just a bit obsessed with finding an actual solution as I know it is out there and as this will arise more and more in future scenarios for me. I've spent an embarrassing amount of time on this (not all is lost because I've discovered a lot of unrelated tools and solutions by accident because of this haha but the fact that I haven't got an answer to this one is driving me a bit mental!)

If I could get any thoughts on the above, I'd be so so grateful! Attached workflow to show the above if that's any helpful. 

[RolandSchubert · Accepted solution]

Hi @VanessaB ,

in my understanding, you want to join Dataset 1 and Dataset 2 on Field 1 = Type and Field 2 = Subtype (joining each record in Dataset 1 to many records in Dataset 2). After this join Final Field is selected depending on matching a part of Field 3 to IDer.

I modified your workflow a bit. First step is a Join tool, second step a Filter tool using CONTAINS to select the "right" of the records created by join. What do you think?

Best,

Roland

[danilang · Accepted solution]

Hi @VanessaB 

You're running to the common issue with the Find replace tool which is that all F rows are match against each R row in sequence so the last matching R is the one that is applied.  To get around this, try this technique

Match on Type and Subtype and then use a Contains Filter to narrow down to the final record

This does give you a large intermediary data set, but your matching table seems to be designed to avoid duplicates so you end up with only the result you're looking for.  Note that Record 4 should be Custody Cash as oppposed to Collateral account

Dan

[fmvizcaino]

Hi @VanessaB ,

One idea is to bring the IDer to your main dataset using find replace tool and then use a join tool.

Let me know if that works for you.

Best,

Fernando Vizcaino

[DiganP]

@VanessaB Pretty simple workflow. First you join all the records that have the same type and subtype. Then you can use the filter tool to filter out that doesn't have any IDer in the Field 3 using the filter tool. Attached is the workflow. Hope it helps!

[VanessaB]

@RolandSchubert

@danileng

@DiganP

Thank you so much for your responses! This was the solution indeed. Goes to show my lack of understanding of the tools and their uses/results. I didn't consider to the approach of expanding and creating a larger intermediary data set and then further work it down to the right match. I guess I was afraid of messing up the input data and wanted something to give the most exacting result at every step.

But this worked so well, I am so happy!! Thank you!

@fmvizcaino 

Thank you for your response and solution. This worked as well, as with the above, I had difficulties seeing past the intermediary use of tools. I will keep yours in mind just the same. Much appreciated!