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

How to find substring of one field in another field?

marlline
8 - Asteroid

Hi Everyone,

 

I have a question, how to find part of one character field in another character field.

 

Following is the example. I tried to find the contacts that have purchased something additional in later purchase i.e. something they did not buy during 1st purchase.

 

I used

Match(right left)=FindString([1st Purchase], [Later Purchase]) and

Match(left right)=FindString([Later Purchase], [1st Purchase]).

 

I found that when Match(left right) = -1, it seems to give me what I wanted. But it cannot differentiate when part of Later Purchase is in 1st Purchase. Please kindly help with your solutions. Thank you so much!

 

1st PurchaseLater PurchaseMatch (right left)Match (left right)Flag
Banana,Juice,KiwiBanana,Kiwi-1-1Wrong
Banana,Juice,KiwiBanana,Juice,Volume Fruit-1-1Fine
Banana,Juice,KiwiBanana-1-1Wrong
Banana,Juice,KiwiJuice,Kiwi-1-1Wrong
Banana,Juice,KiwiBanana,Juice-1-1Wrong
Banana,Juice,KiwiStarfruit-1-1Fine
4 REPLIES 4
MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @marlline,

 

I'm caught up in something now, but here is a quick workflow to get you started.

 

  1. Assign RecordID
  2. Parse to Rows (1st purchase items)
  3. Parse to Rows (2nd purchase items)
  4. Lookup (Find & Replace) if 2nd item is in 1st list - no match = Null()
  5. Formula to assign False or True to match
  6. Summarize to find WORST match for a record
  7. Each RecordID contains a match flag

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
DanM
Alteryx Community Team
Alteryx Community Team

@marlline The reason why you are having the issue is that it is taking the entire string attempting to match it. If you parse the Later Purchase to rows and then use a contains statement in the Formula tool, you will see that the expression will designate if the Later Purchase is located in the 1st Purchase. You can then move forward with your next step.  Attached workflow 

marlline
8 - Asteroid

I tried it and it works perfectly!!!

 

THANK YOU SO MUCH @DanM!

 

Your-awesome-clipart.jpg

JohnDanahy
5 - Atom

Brilliant solution, thank you!

Labels