Removing duplicates based on other columns

Geoff_L

Hi Alteryx community

I’ve been using Alteryx for only 4 weeks and am stuck.

I’m working on a project comparing clients who use various new services with a normative sample of clients who do not use the new services.

Unfortunately, the normative data is mixed in with the rest of the new service data. Those who use the new services are are still identified as “Normative Cohort”. 

Client	PROGRAM	YEAR	SEASON
Bob	PEET	2017	Autumn 2017
Bob	PEET	2017	Spring 2017
Bob	PEET	2018	Autumn 2018
Bob	PEET	2018	Spring 2018
Bob	Normative Cohort	2017	Autumn 2017
Jane	DESY	2017	Autumn 2017
Jane	DESY	2017	Spring 2017
Dave	Normative cohort	2017	Autumn 2017
Dave	Normative cohort	2017	Spring 2017
Dave	Normative cohort	2018	Autumn 2018
Dave	Normative cohort	2018	Spring 2018
Sally	CHOP	2017	Autumn 2017
Sally	CHOP	2017	Spring 2017
Sally	CHOP	2018	Autumn 2018
Sally	CHOP	2018	Spring 2018
Sally	Normative Cohort	2017	Autumn 2017

Somehow I need to keep the “Normative Cohort” cases in the data when they are truly part of the normative cohort but remove them for clients who have attended one of the new services. I’ve highlighted examples of cases that need removing. Bob has attended the PEET program and Sally has attended the CHOP program but both are also recorded as Normative Cohort in the same Season.

Can anyone please help me with this? Thanks.

Normative_Service_help.xlsx

danrh

Something like this should get you there:

In the Multi-Row Formula tool I'm checking two conditions: first, is the current row a "Normative" row, and second, is either the row above or below filled in. Because I'm grouping by Client and Season, this will determine seasons where a client was part of a "Normative" group AND another group and flag the Normative row. Then a Filter to pull these out, and a Select to remove the extra field we created with the Multi-Row Formula.

Hope it helps!

solution.yxmd