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

[Request] Identifying Amount of Matches in a Set of Record Using a List

Rice
5 - Atom

Hi,

 

Scenario: I have a list of Actions that are assigned to various users within the organization. We use those Actions to identify where the concentration of work is distributed. It has been requested by a specific organization to run the Action Owners against their list of employees. The result(s) would allow us to verify if people are properly assigned and Actions that need review.

 

I have a list of 50 employees and need to see if there is any mention of any employee in a field list of another set of data.

Using alphabets for easy reference:

Set A: [A,B,C,D,E,F...] (Call it 26 unique names)

 

Set B:[AB,A,1,2,4,6,Z,E,F]

The result would help me identify that AB, A, Z, E, F have matches to Set A. Let's assume the numeric numbers are other employees in other organizations.

 

I have found one solution similar to this but isn't the most sexy, scale-able, easy to comprehend for non-Alteryx users and that is: having my first column be the list I want to check for matches then appending to the first row the list of employees I'm checking - doing a multi-field formula which would count for any hits - summing and then filtering down.

 

Suggestions on other methods?

 

Thank you in advance!

 

6 REPLIES 6
Claje
14 - Magnetar

Hi,

I'm not sure exactly what you're doing with the data after you find matches, so the attached does not do any final summarization, but I think it should get you close to what you need.


The added benefit is that it should be much faster on large datasets than doing a full Append of all employees to ALL Actions.

 

I tried to include some notes on why I included each step in this example.  Hope it helps!

MarqueeCrew
20 - Arcturus
20 - Arcturus

@Claje,

 

What an impressive workflow you built.  I have simplified the process with this:

 

capture.jpg

 

Since @Rice only has 50 employees, let's just append each set A record to every set B record (* 26 in this workflow).  Now filter for where B contains A and cleanup with a select and UNIQUE (AB matches 2 names).  

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Rice
5 - Atom

Thank you very much - the picture helped me scan quite easily to apply it to my data set.

 

The issue I had was overcoming that there aren't m(any) tools that allow for contains and your solution was able to stop me in the middle of a transposing and cross-tab nightmare.

Claje
14 - Magnetar

Nice workflow name @MarqueeCrew ;)

MarqueeCrew
20 - Arcturus
20 - Arcturus
Thanks for the feedback and for asking your question. Please do note that Jessie's workflow also solves the challenge. While poking a little fun at the #1 Certified Alteryx guru, I do encourage lots of alternative solutions and hope that you'll mark his as a solve too ( along with any future good suggestions).

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus
Ps: I have a macro that searches for a list of terms.

https://gallery.alteryx.com/#!app/Contains-In-List/5a5903fda18e9e2508e5ae0d

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels