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

Alteryx Designer Discussions

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

Need help with complex filtering

NabilA
6 - Meteoroid

Hi Guys

 

I have a data table like this:

 

IDemailactiontopicdate
ID01Tom@a.comJoinMaths01-Jan
ID02Tom@a.comLeftMaths28-Jan
ID03Tom@a.comJoinMaths01-Feb
ID04Tom@a.comLeftMaths12-Feb
ID05Tom@a.comJoinMaths30-Mar
ID06Tom@a.comCompletedMaths02-Apr
ID07Sue@a.comJoinedMaths05-Jan
ID08Sue@a.comLeftMaths06-Jan
ID09Ben@a.comJoinedMaths08-Feb
ID10Ben@a.comCompletedMaths12-Feb
ID11English

 

 

That I want to turn into this filtered data:

 

emailactiontopicdate
Tom@a.comJoinMaths30-Mar
Tom@a.comCompletedMaths02-Apr
Ben@a.comJoinedMaths08-Feb
Ben@a.comCompletedMaths12-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?

 

Many thanks in advance,

 

Nabil

Jonathan-Sherman
15 - Aurora
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:

 

Jonathan-Sherman_0-1617884366709.png

 

 

Kind regards,

Jonathan

NabilA
6 - Meteoroid

Hi Jonathan,

 

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

 

Thanks,

 

Nabil

 

 

Labels