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 files by partial field similarities

Vinesh
5 - Atom

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.

NameWEEK
Apple34
Pear35
Banana35
Strawberry37
Peach38

 

Document 2.

NameWEEK
Chocolate - Belgian41
ABCD Pear - 5432142
Bananasplash - warm43
Strawberry - Applepie44
NWCOApple - 1234545

 

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!

6 REPLIES 6
vishwa_0308
11 - Bolide

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
13 - Pulsar

Give the attached a try:

image.png

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
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Vinesh
5 - Atom

That is indeed true Mark!

 

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

 

Document 1.

NameWEEK
Apple44
Pear42
Banana43
Strawberry44
Peach38

 

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
13 - Pulsar

That makes it a little more interesting.

 

image.png

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
5 - Atom

Thanks! Such a great solution.

Labels