how can we join multiple fields using like operator from multiple data sets

[mval]

how can we join multiple fields using like operator from multiple data sets

[bpatel]

hi @mval ,

the Join multiple or Join can help you accomplish this.

Join multiple:

https://community.alteryx.com/t5/Alteryx-Designer/Tool-Mastery-Join-Multiple/ta-p/124619

Join:

https://community.alteryx.com/t5/Alteryx-Designer/Tool-Mastery-Join/ta-p/29814

i hope this helps!

[DannyS]

If you are trying to match fields with similar but not exact record values (e.g. joining First/Last names on Nicknames like Bob Smith on Rob Smith) I would suggest checking out our documentation on Fuzzy Matching. You can find help reference to Fuzzy Matching in AYX here: 

https://help.alteryx.com/2018.2/FuzzyMatch.htm

https://community.alteryx.com/t5/Alteryx-Designer/Tool-Mastery-Fuzzy-Match/ta-p/45485

[mval]

hi @bpatel, I am looking for like operator join but not equi join. As an example I want to compare "Table1 Name" using like with "Table 2 Name". Same thing I have to do with multiple fields. 

Table1

Name         Year    x      y
John Smith   2010    10     12
Adam Jones   2010    8      13
John Smith   2011    7      15

and

Table2

Name                    Year  z    
Smith John Smith John   2010  27
Jones Adam Jones Adam   2010  25
Smith John Smith John   2011  29

[mval]

Hi Danny,

Based on the below example, please help on how to approach this problem. Thank you.

[bpatel]

hi @mval ,

A find and replace should help you. i mocked up a workflow. hope this helps!

[mval]

Thank you @bpatel. we can use Find Replace with one column comparison. I have to do the similar comparison for more than one column.

Like between "Table1 Name" and "Table2 Name" and "Table1 City" and "Table2 City". Thank you.

Table1

Name         City        x      y
John Smith   New York    10     12
Adam Jones   Paris        8      13
John Smith   Zurich        7      15

and

Table2

Name                    City           z    
Smith John Smith John   New York City  27
Jones Adam Jones Adam   Paris City           25
Smith John Smith John   Paris City           29

[bpatel · Accepted solution]

hi @mval ,

in that case the fuzzy match should help. i attached the updated workflow

[mval]

Thank you @bpatel. I don't see 3rd record in Fuzzy output, how can we incorporate it. What should we change in the config. of Fuzzy?

[mval]

I got it. Thank you both @bpatel and @Danny