Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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

2 REPLIES 2
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