ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
alteryx Community

# Alteryx Designer Discussions

SOLVED

## Need help with complex filtering

6 - Meteoroid

Hi Guys

I have a data table like this:

 ID email action topic date ID01 Tom@a.com Join Maths 01-Jan ID02 Tom@a.com Left Maths 28-Jan ID03 Tom@a.com Join Maths 01-Feb ID04 Tom@a.com Left Maths 12-Feb ID05 Tom@a.com Join Maths 30-Mar ID06 Tom@a.com Completed Maths 02-Apr ID07 Sue@a.com Joined Maths 05-Jan ID08 Sue@a.com Left Maths 06-Jan ID09 Ben@a.com Joined Maths 08-Feb ID10 Ben@a.com Completed Maths 12-Feb ID11 … … English …

That I want to turn into this filtered data:

 email action topic date Tom@a.com Join Maths 30-Mar Tom@a.com Completed Maths 02-Apr Ben@a.com Joined Maths 08-Feb Ben@a.com Completed Maths 12-Feb … … English …

What I've tried to do :

I used Sort by date decending followed by Unique tool to get a list of all unique email+action+topics

But this still contains Row ID07 when Sue joined the course.

I created a list that had a Filter tool (set to [action] = Left) as an input to a Join tool as a filter, but then it removed ID01 Tom and ID07 Sue from my list.

I feel like I'm missing something obvious here. To put it simply, for each [topic].[action] = "Left" I want to remove a [topic].[action] = "Joined" in ascending date order.

Any suggestions?

Nabil

15 - Aurora

Hi @NabilA,

Perhaps something along the lines of the following? I've filtered for where [action] = "completed". Kept only lines where the email has completed the course:

Kind regards,

Jonathan

6 - Meteoroid

Hi Jonathan,

Thanks for providing a solution so fast! It's elegant and simple. 👍

Thanks,

Nabil

Labels