Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

VanessaB
5 - Atom

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:

VanessaB_3-1576515771482.png

 

'Mapping' file:

VanessaB_7-1576516307167.png

 

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:

 

VanessaB_5-1576516143615.png

 

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! 

3 REPLIES 3
cplewis90
13 - Pulsar
13 - Pulsar

Probably not exactly what you were looking for or the best when it comes to processing, but you could join on the first two criteria and then use a filter to get the right contain option. Another option would be to create a REGEX parse to grab the info needed from the 3rd field and then do a straight join. I have attached the first option as doing a REGEX formula would require a bit more knowledge of the data to identify the pattern.

 

Multiple join conditions.PNG

DiganP
Alteryx Alumni (Retired)
VanessaB
5 - Atom

@cplewis90 

 

Thank you so much!!

 

As per the post below, this is actually a duplicate post. However! After having tried the solutions in the previous post, it did work for one part of my workflow. Unfortunately, when it came to another part with more complex and convoluted conditions/fields requirements, it actually stopped working. Your solution I think is actually the answer for what I will need in other scenarios though as you said, I'd need to become more acquainted with regex. 

 

Again, many many thanks! I will be taking away and building on further to my data set and see what I can do from this. Much appreciated. 

 

@DiganP Apologies, the first post actually didn't show up for about 45 minutes, I thought I didn't click post the first time. I'll be more mindful in the future, thanks!

Labels