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

Simpler Find/Replace String in Field or Field Name?

cbridges
11 - Bolide

In an attempt to anonymize my data, I'm using a multi-field formula with REGEX to replace customer names in the data, and a dynamic rename with REGEX to replace customer names in field headers, like this:

 

Multi-Field:

IF

findstring([_CurrentField_], “Cust1”)!=-1 then

REGEX_Replace([_CurrentField_], “Cust1”, "John")

elseif findstring([_CurrentField_], “Cust1”)!=-1 then

REGEX_Replace([_CurrentField_], “Cust1”, "Paul")

else [_CurrentField_] endif

 

Dynamic Rename:

IF
findstring([_CurrentField_], "Cust1")!=-1 then
REGEX_Replace([_CurrentField_], "Cust1", "John")
elseif findstring([_CurrentField_], "Cust2")!=-1 then
REGEX_Replace([_CurrentField_], "Cust2", "Paul")
else [_CurrentField_] endif

 

The thing is, I can get away with this because I'm only replacing a small number of strings. But what if there were many strings to replace?

 

Is there a simpler way to accomplish this than 2 full paragraphs of REGEX code?

 

@thizviz
9 REPLIES 9
jdunkerley79
ACE Emeritus
ACE Emeritus

I would try something like Transpose to make it one field for all that I need to replace

Then use a find and replace tool to replace the entries

FInally use a Cross Tab tool to put back to original layout.

 

You might want to use a record ID to make the join back easier.

 

Just a quick idea but think would work

cbridges
11 - Bolide

I'm not sure if making it one field would make too much of a difference... basically it's already one field because I'm using the multi-field tool, so it's always "current field". I was mostly wondering if there's a less verbose solution, either using a lookup table of some kind or if there's some way to word the formulas differently. This was my 4th or 5th attempt, because I soon found out that a simple Replace(String, Target, Replacement) didn't work.

 

What would be super cool is if some wizard out there made an anonymizer macro, where you could quickly group the fields you need to change and then it would add a column for replacement value (maybe even automatically generated like Cust001, Cust002 etc.), then create a way to replace one value with the other throughout. Hmm... sounds like a good side project for me to try.

@thizviz
jdunkerley79
ACE Emeritus
ACE Emeritus

Did a simple thing for masking names in data. If this is what you mean will do one for fields

 

This will create a unique ID for each name and replace the sepcified fields with Custxxx for it.

 

Can also be done on Field Names as well. Attached a second sample showing masking fieldnames. Masked ID are consistent on both data sets

cbridges
11 - Bolide

Wow! I completely don't get what you did here, but it's impressive and fast!

 

It's 99.9% of what I'm looking for, so I'm marking it as correct and I encourage you to turn it into an anonymizer macro, because I think everyone would love you for that.

 

The only way in which it isn't perfect for me yet is that I already have one unique field, which is "customer - store#" and I'm not sure how to retain the store# part but keep the customer part (see last column in results). I'm going to send back the workflow with my pre-anonymized data. Maybe you can be the magic macro maker!

@thizviz
cbridges
11 - Bolide

OK I think all I need to do with yours is re-create my unique store identifier by recombining the new customer name with the store number.

 

I'm going to try again and see what I get...

@thizviz
cbridges
11 - Bolide

It works great for the data masking - can the name masking be modified to replace the customer name as part of a string rather than the whole field? Like "Alteryx Sales Area" and "Tableau Sales Area" would change to "Cust001 Sales Area" and "Cust 002 Sales Area"? If so, I dub thee Gandalf II.

@thizviz
jdunkerley79
ACE Emeritus
ACE Emeritus

Try the attached macro.

 

This can be used to mask columns selectively. It takes in a data set and masked table set. Any record not found get added to masked set and then the resulting data set and unioned masked data is returned.

 

Field name masking is another process but did it by taking output of Cust mask.

 

Let me know what you think.

cbridges
11 - Bolide

Wowwwwwwwwwww!!!! Genius! I hearby dub thee Gandalf II, the Great Wizard of Alteryx!!

Smiley Very Happy

 

You should publish and advertise this, I think. It's magic!

@thizviz
cbridges
11 - Bolide

PS it worked on my original raw file with no configuration necessary!

@thizviz
Labels