Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Regex(?) formula to remove values matching the ones stored in other field

kwieto
8 - Asteroid

Check two field, each containing multiple values, separated by "|" sign, against each other and list differences for each field.

So basically if field 1 has values like ABC|DEF|GHI and Field 2 has DEF|JKL I want to have two outputs:
For Field 1 - ABC|GHI (values which are not available in Field 2)
For Field 2 - JKL (value not available in Field 1)

 

I know that I can split both fields into rows, then use join to match same values and have differences listed in L and R output, but just for the sake of playing I am trying to do the same thing via formula. 
I created one which is basically Regex_Replace([Field 1], [Field 2], "") which, since the "|" sign stands for OR in RegEx, basically works. - in the above case it will translate to Regex_Replace("ABC|DEF|GHI", "DEF|JKL", "") and return ABC||GHI (it requires cleaning unnecessary "|" signs afterwards, but that's not a problem)

The problem is when we have two similar values, like "Name" and "First Name" in that case, if one field will contain "First Name" and the second one just "Name", the end result will be "First " instead of "First Name".

Any Ideas how to modify the formula to properly cover such cases as well?


11 REPLIES 11
kwieto
8 - Asteroid

@Qiu that would be too easy 😅
In general I use text to column by adding identifier, then splitting both columns separately with text to column tool (but using split too rows option) and joining both streams, but I am really curious if it can be achieved via formula

 

@flying008 - great solution!
I crafted something similar by myself, using ReplaceChar as support:

ReplaceChar(Regex_Replace(ReplaceChar([Field 1], " ", "_"),"\b(" + ReplaceChar([Field 2], " ", "_") + ")\b",""), "_", " ")

Both formulas give same output, but there is one tweak - if the value has a special character at the end or the beginning (i.e. parenthesis) It will not work.
So for example if the value like "Banana Rotten Extremely" would be "Banana Rotten (Extremely)" it won't match anymore.

One thing is that Regex consider parenthesis as special sign, so the pattern string must be modified to "Banana Rotten \(Extremely\), but I see also that the special character at the end disturbs the "\b" operator.
Probably i can wrap another Replace/Regex_Replace function to change parenthesis to some other character, but it starts to be an overkill 😂
But for simple texts it works quite efficient

flying008
15 - Aurora

Hi, @kwieto 

 

Any pattern can be addressed with regular expressions ! 😁

Labels
Top Solution Authors