Alteryx designer Discussions

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

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

Highlighted
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!

 

Highlighted
ACE Emeritus
ACE Emeritus

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!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
Highlighted
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.

Highlighted
ACE Emeritus
ACE Emeritus

Nice workflow name @MarqueeCrew ;)

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
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 reboot. Order shall return.
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
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 reboot. Order shall return.
Labels