community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Removing duplicates based on other columns

Highlighted
Atom

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.

Pulsar

Something like this should get you there:

image.png

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!

Atom

Hi danrh

 

Thanks so much! That worked perfectly. I'm so grateful for your help with this. I was really stuck but now the project is moving forward.

 

Warmly - Geoff

Labels