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

Finding duplicates with conditions

SCK
8 - Asteroid

Hi All. I have one more request I need help with. I need to find the list of different assignees for same type of data across different unit. The input and desired output is as follows:

 

Input:

 

UnitDataPlayer1 RolePlayer1Monitor RoleMonitorSubstitute RoleSubstitute
AName1OwnerClairePreparerMichaelOwnerClaire
BName2PreparerMichaelOwnerClaireManagerSean
CName1CheckerBenCheckerBenManagerSean
DName4ManagerSeanCheckerBenOwnerClaire

 

Output:

UnitDataPlayer1 RolePlayer1Monitor RoleMonitorSubstitute RoleSubstitute
AName1OwnerClairePreparerMichaelOwnerClaire
CName1CheckerBenCheckerBenOwner

Claire

 

In the above example for the same data (name1) the assignees across different unit are different, which should have been the same. So, I need to find it as an output. Please help.

3 REPLIES 3
atcodedog05
22 - Nova
22 - Nova

Hi @SCK 

 

Adding to the previous solution.

Here is how you can do it.

 

1.Transpose so that all names comes in a single column. 

2. Use summarize to find the count of occurrence for each name

3. Keep only the ones which occur more than once

4. Using summarize tool to remove duplicate occurrence of Unit and Data (removing duplicates is important to prevent one to many joins)

5. Join it back with the main data on Data and Unit

 

Workflow:

atcodedog05_0-1609696944315.png

 

Hope this helps 🙂 Feel to ask if you have any questions

BrandonB
Alteryx
Alteryx

You can use a summarize tool to group by the Data column and also count the data column. Then follow the summarize tool with a filter tool where the count > 1. Then you can join this back to the data before the summarize on the Data column. Only the records where there is more than one row per name will return in the inner join. 

Edit: @atcodedog05 great minds think alike!

 

Another approach is the “only unique” as explained here: http://www.chaosreignswithin.com/2014/08/only-unique-macro.html?m=1

SCK
8 - Asteroid

Thank you for the help. I just had to tweak it a bit to make it work as per my requirement. 🙂

Labels