Alteryx Designer Discussions

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

Match One Value between Two Strings

Bonediggler
8 - Asteroid

Experts - 

 

Here's the case: I need to figure out if there is at least one common value present in 2 different strings.  For example:

 

String A: 123, abd, edf

String B: 345,edf,789

 

In this case there is a match on "edf". 

 

I am looking for a solution that:

> Doesn't involve parsing and then transposing the strings as that will create millions of records

> Leaves the strings as they are

> Doesn't involve lots of tools - as I already have a solution that involves lots of tools

 

If I am overlooking an existing function or simple process to get this done, please let me know!

 

Thanks in advance!

20 REPLIES 20
atcodedog05
20 - Arcturus

Hi @Bonediggler 

 

I didnt know that these were 2 columns. If so here is a modification of @Maskell_Rascal to optimize it further.

atcodedog05_0-1627583993214.png

 

You can split only one column and use contains to check if that keyword exists in other. This drastically reduced the number of row.

 

Hope this helps : )

 

Maskell_Rascal
13 - Pulsar

Hope it works, and good luck! 😁

Maskell_Rascal_0-1627584183209.gif

 

atcodedog05
20 - Arcturus

@Maskell_Rascal your GIF is not convincing to the Good luck though 😅

Bonediggler
8 - Asteroid

Nice improvement on the prior solution!  I would note however that using a multi-field tool to get rid of white space is generally much more performant than the data cleansing tool.  

 

Either way, thanks!

atcodedog05
20 - Arcturus

@Bonediggler 

 

True that use Mult-field tool improves performance over using data cleanse tool.

Maskell_Rascal
13 - Pulsar

I avoid the Data Cleansing tool at all costs. It is the single greatest performance killer in any of my workflows. 

john_watkins
10 - Fireball

Here is an example that also does no parsing using REGEX.  I need to force myself to get better with this tool, but this works as well and generates no rows.multicolumntextcompare.PNG

atcodedog05
20 - Arcturus

Nicely done @john_watkins 🙂 👍

 

Checking for duplicate words. Why didn't I think about that😅

Bonediggler
8 - Asteroid

Well done!  This definitely gets us around the exploding rows problem.  Unfortunately using the regex logic is significantly less performant, at least with the data set I am using.  Can't have your cake and eat it too I suppose....

Maskell_Rascal
13 - Pulsar

@john_watkins - I had to come back to this post again just to say that this is an awesome solution! I will definitely be bookmarking this for reference later. 🙌😀

Labels