Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Find and Replace Using 2 Fields

Highlighted
8 - Asteroid

Please see attached example.  I'm trying to find the value in Field2 from Field1 and replace it with nothing.  The DesiredOutput is what I'm trying to achieve.  It must be case insensitive as well.

 

thank you in advance!

Highlighted
12 - Quasar

 

@bertal34 — Try attached the workflow:

 

REGEX_Replace([Field1], "\s"+[Field2]+"\b", "")

 

vizAlter_0-1600267699241.png

 

 

 

 

 

 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@bertal34 ,

 

The following process parses each word and compares it to the second field value.  If the field 2 value appears once or more, it will be dropped.  The resulting words get concatenated back together.

 

capture.png

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Certified Partner

Hi @bertal34 ,

 

I've packaged this into a macro for you:

 

mceleavey_0-1600267869432.png

 

mceleavey_1-1600267882846.png

You simply need to group the fields in the macro by the original field, and on the "Questions" tab, map field one and the field containing the text you want to replace. Then it will run for all values.

 

mceleavey_2-1600267968104.png

 

 

Hope this helps.

 

M.

 

Highlighted
8 - Asteroid

@vizAlter  How would you alter to only replace the last word in Field1?

Highlighted
12 - Quasar

@bertal34 — Use below in Formula if you just want to remove the last string from your cell:

 

 

REGEX_Replace([Field1], "(.*.)(\<\w+\>$)", "$1")

 

 

Then, it does not need [Field2]

And, you may treat this RegEx function with IF..ELSE..ENDIF to manipulate accordingly, let me know if this answers your query.

 

And, if you just want to fetch the last word, then you may also use the inbuilt functions, see below example for your reference:

 

ReverseString(GetWord(ReverseString([Field1]),0))

 

then function below will give you the same result (it will remove the last word):

Trim(Left([Field1], Length([Field1])-Length([GetLastWord])))

 

(I have created a couple of examples in enclosed workflow, if you want)

 

 

If it resolves your query please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution.  Thank you.

Highlighted
12 - Quasar

@MarqueeCrew — Without using any Formula expression.... really interesting & cool solution!

@mceleavey — I liked your solution too!

@bertal34 — Thank you!

Highlighted
8 - Asteroid

Thank you all for your assistance.  Greatly appreciated!!

Labels