Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Remove not duplicates

Ultralightbeam
8 - Asteroid

Hello guys I want to delete entire row with single entry

Input:

NameIDDateGrade
Alyssa12021-01-224
Alyssa262021-02-055
Alyssa452021-01-084
Dy62021-01-224
Edrose332021-02-055
Fatima92021-01-225
Fatima372021-02-055

 

Desired Output:

NameIDDateGrade
Alyssa12021-01-224
Alyssa262021-02-055
Alyssa452021-01-084
Fatima92021-01-225
Fatima372021-02-055
18 REPLIES 18
echuong1
Alteryx Alumni (Retired)

You can use the Only Unique Crew macro. The non-duplicates will come out of the U, and duplicates out of the D. 

 

echuong1_0-1613573015980.png

 

Emil_Kos
17 - Castor
17 - Castor

Hi @Ultralightbeam,

 

I have prepared a workflow for you:

 

Emil_Kos_0-1613573028491.png

 

 

I am checking how many times the name is occurring in the data and we removed everything that occurred only once.

 

Emil_Kos_1-1613573055801.png

 

If this workflow is helpful please mark my post as a solution.

 

apathetichell
19 - Altair

Summarzie tool (Group by Name, Count Name). Join the results to your original database with Count as a new column. Filter by Count > 1. That should give you what you need.

Ultralightbeam
8 - Asteroid

Hi @Emil_Kos, I forgot the main issue why I posted this. 

Input:

NameProviderDateGrade
AlyssaNico2021-01-224
AlyssaShaira2021-01-225
AlyssaShaira2021-02-055
AlyssaNico2021-01-084
DyNico2021-01-224
EdroseNico2021-02-055
FatimaShaira2021-01-225
FatimaNico2021-02-055

 

Desired Output:

NameProviderDateGrade
AlyssaShaira2021-01-225
AlyssaShaira2021-02-055
AlyssaNico2021-01-084
FatimaShaira2021-01-225
FatimaNico2021-02-055


so if the name has two provider, it should get the max grade.

Emil_Kos
17 - Castor
17 - Castor

Hi @Ultralightbeam,


Sure give me a second. 

Emil_Kos
17 - Castor
17 - Castor

Hi @Ultralightbeam,


I am not sure why you remove this line from the data:

 

Alyssa Nico 2021-01-08

4

 

As this combination isn't unique.

 

 


Please let me know if this workflow works for you

Ultralightbeam
8 - Asteroid

hi @Emil_Kos 

AlyssaNico2021-01-08

4

is still included on the desired output


I think based on your output its still the same

AlyssaNico2021-01-224
AlyssaShaira2021-01-225

 both of this has been output but since both weeks are the same - we should get the max grade. Do you think it's possible? I've been doing this for two hours already 

Emil_Kos
17 - Castor
17 - Castor

Hi @Ultralightbeam,


Maybe I am missing something but I don't see any changes between your input and the desired output.


Can you highlight to me where do you see any changes between the two data sets? 

Ultralightbeam
8 - Asteroid

@Emil_Kos

 

I have elaborated the criteria on the comment column, thank you very much 

Input:

NameProviderDateGradeComment
AlyssaNico2021-01-224This should be removed since it is the same date with the next row and grade is smaller.
AlyssaShaira2021-01-225Retain since higher than duplicate date
AlyssaShaira2021-02-055Retain since date is unique for alyssa
AlyssaNico2021-01-084Retain since date is unique 
DyNico2021-01-224This will be removed since it is only one - I am only including rows that has two or more grades.
EdroseNico2021-02-055Same from row above
FatimaShaira2021-01-225Retain
FatimaNico2021-02-055Retain

 

Desired Output:

NameProviderDateGrade
AlyssaShaira2021-01-225
AlyssaShaira2021-02-055
AlyssaNico2021-01-084
FatimaShaira2021-01-225
FatimaNico2021-02-055
Labels
Top Solution Authors