join files by partial field similarities

[Vinesh]

Hello all,

I have a question regarding joining two files based on partial similarities. And if the match has been made to change the week number.

Example:

Document 1.

Name	WEEK
Apple	34
Pear	35
Banana	35
Strawberry	37
Peach	38

Document 2.

Name	WEEK
Chocolate - Belgian	41
ABCD Pear - 54321	42
Bananasplash - warm	43
Strawberry - Applepie	44
NWCOApple - 12345	45

Now I would like to check if the names of document 1 can be found in the name column of document 2. And if the name has been found, that the week number of document 2 is changed to the week number of document 1.

And what will happen to the Strawberry - Applepie, which contains both Strawberry and Apple?

Thanks!

[vishwa_0308]

Hi @Vinesh,

You can use Find replace with partial matching. Keep Doc 2 in the F input and Doc 1 in the R. It works like V look up in excel, will simply replace the field based on your matching criteria.

Thanks,

Vishwa

[danrh]

Give the attached a try:

It looks like where there are two matches it keeps whichever is listed second in Document 1 (so in this case Strawberry - WEEK 37), though I don't know that this is a strict rule, just an observation.

Hope it helps!

[MarqueeCrew]

@Vinesh,

I would suggest that you should describe what you want for a result, rather than acceptance of "what will happen".  You can configure Alteryx to act a certain way when one match to document 2 exists and then decide what to do when multiple matches occur.  Perhaps when multiple matches occur you look at the WEEK number or another data field to break ties or you use the placement of the data (begins with versus ends with) to decide upon the match.

Cheers,

Mark

[Vinesh]

That is indeed true Mark!

What I would like to see as result is the following:

Document 1.

Name	WEEK
Apple	44
Pear	42
Banana	43
Strawberry	44
Peach	38

This means that the last matching word is "dominant" one to be used to replace the week number.

I also made a mistake in my question. The week number of document 1 has to be changed, not document 2.

[danrh · Accepted solution]

That makes it a little more interesting.

This replaces the field in each of the given strings, parses them out individually, and adds the max week back to Document 1.  I'm making some assumption regarding "last matching word" here, take a look and see if this gets you closer.

[Vinesh]

Thanks! Such a great solution.