topic Re: Remove not duplicates in Alteryx Designer Desktop Discussions

Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 14:40:22 GMT

Hello guys I want to delete entire row with single entry

Input:

Name	ID	Date	Grade
Alyssa	1	2021-01-22	4
Alyssa	26	2021-02-05	5
Alyssa	45	2021-01-08	4
Dy	6	2021-01-22	4
Edrose	33	2021-02-05	5
Fatima	9	2021-01-22	5
Fatima	37	2021-02-05	5

Desired Output:

Name	ID	Date	Grade
Alyssa	1	2021-01-22	4
Alyssa	26	2021-02-05	5
Alyssa	45	2021-01-08	4
Fatima	9	2021-01-22	5
Fatima	37	2021-02-05	5

Re: Remove not duplicates

echuong1 — Wed, 17 Feb 2021 14:44:11 GMT

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

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 14:44:53 GMT

Hi @Ultralightbeam,

I have prepared a workflow for you:

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

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

Re: Remove not duplicates

apathetichell — Wed, 17 Feb 2021 14:46:00 GMT

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.

Re: Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 15:09:23 GMT

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

Input:

Name	Provider	Date	Grade
Alyssa	Nico	2021-01-22	4
Alyssa	Shaira	2021-01-22	5
Alyssa	Shaira	2021-02-05	5
Alyssa	Nico	2021-01-08	4
Dy	Nico	2021-01-22	4
Edrose	Nico	2021-02-05	5
Fatima	Shaira	2021-01-22	5
Fatima	Nico	2021-02-05	5

Desired Output:

Name	Provider	Date	Grade
Alyssa	Shaira	2021-01-22	5
Alyssa	Shaira	2021-02-05	5
Alyssa	Nico	2021-01-08	4
Fatima	Shaira	2021-01-22	5
Fatima	Nico	2021-02-05	5

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

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 15:10:05 GMT

Hi @Ultralightbeam,

Sure give me a second.

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 15:18:41 GMT

Hi @Ultralightbeam,

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

Alyssa

Nico

2021-01-08

As this combination isn't unique.

Please let me know if this workflow works for you

Re: Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 15:25:23 GMT

hi @Emil_Kos

Alyssa

Nico

2021-01-08

is still included on the desired output

I think based on your output its still the same

Alyssa	Nico	2021-01-22	4
Alyssa	Shaira	2021-01-22	5

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

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 15:29:42 GMT

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?

Re: Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 15:35:55 GMT

@Emil_Kos

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

Input:

Name	Provider	Date	Grade	Comment
Alyssa	Nico	2021-01-22	4	This should be removed since it is the same date with the next row and grade is smaller.
Alyssa	Shaira	2021-01-22	5	Retain since higher than duplicate date
Alyssa	Shaira	2021-02-05	5	Retain since date is unique for alyssa
Alyssa	Nico	2021-01-08	4	Retain since date is unique
Dy	Nico	2021-01-22	4	This will be removed since it is only one - I am only including rows that has two or more grades.
Edrose	Nico	2021-02-05	5	Same from row above
Fatima	Shaira	2021-01-22	5	Retain
Fatima	Nico	2021-02-05	5	Retain

Desired Output:

Name	Provider	Date	Grade
Alyssa	Shaira	2021-01-22	5
Alyssa	Shaira	2021-02-05	5
Alyssa	Nico	2021-01-08	4
Fatima	Shaira	2021-01-22	5
Fatima	Nico	2021-02-05	5

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 15:47:52 GMT

Hi @Ultralightbeam,

Can you review and let me know if this works:

Output:

Re: Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 15:51:04 GMT

@Emil_Kos Thank you so much for saving me!! Have a great day!!

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 15:53:18 GMT

Hi @Ultralightbeam,

No worries. Actually, without your detailed explanation, I would never complete it.

I wished more people would provide so granular level of detail.

Wish you luck and I hope I will have the opportunity to help you more in the future.

Re: Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 16:11:25 GMT

@Emil_Kos I just notice that if the provider is at bottom part it shouldn't be included on the output.

I have included sort by provider

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 16:09:25 GMT

Hi @Ultralightbeam,

That means it works for you now or you need any help? 😆

Re: Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 16:14:14 GMT

@Emil_Kos

I am about to give up lol

Output should still be the same as yours even a row with same provider is at bottom part

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 16:16:27 GMT

Hi @Ultralightbeam,

I made small change in the sort order

Re: Remove not duplicates

Ultralightbeam — Wed, 17 Feb 2021 16:39:13 GMT

I just figured out i was ascending a different column.

its 12 am here and been doing alteryx for more than a day now. Thanks for your help I will sleep now and continue tomorrow.

Re: Remove not duplicates

Emil_Kos — Wed, 17 Feb 2021 16:43:33 GMT

Hi @Ultralightbeam,

Sleep well 🙂

I am not sure if due to the time zone difference I will be able to help you but feel free to shout for me if needed 🙂