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

Unique records in multiple fields - need to return specific record based on conditions

TaniaC
7 - Meteor

Hi – hoping I can get some help here on something I am sure is very basic (but I am new to Alteryx Designer and just finding my way through).

 

I am trying to analyse large sets of training records, and in one step, I need to delete duplicates where a user has the same status multiple times for the same course. Should be easy, but the twist is I also need to ensure that – if the user has both “Complete” and “Incomplete” status, that only the “Complete” status flows through in the unique records.

 

I have managed to use the “Unique” tool to cull out all records where there are multiple cases of the same completion status (i.e. user 1 and course X has “complete” twice so I only take one record forwards) by looking for unique values across all three columns,  but if I take out the course ID from the unique tool, the result sometimes gives me the “Incomplete” record rather than the “Complete” record that I need.

 

I have tried sorting the records in an earlier step to make sure that the first record is the “Complete” one, but this hasn’t worked and the allocation of whether “Incomplete” or “Complete” flows through to the next step varies from person to person. There is obviously something else in there affecting the result but I don’t know what it is.

 

Any and all help to get me to the required outcome would be GREATLY appreciated. I am rapidly disappearing down the rabbit-hole of “trying everything I can think of and getting nowhere”!

 

(I am using Alteryx 2019.4 and have no opportunity to move to a later version (if there is one) as this is a work computer).

6 REPLIES 6
jacob_kahn
12 - Quasar

Hi @TaniaC 

 

Why can't you first filter out all of the Incomplete records? Do you take the incomplete status if there is no complete status? 

TaniaC
7 - Meteor

Hi - thanks for the reply. Yes, if the only status for a given person on a given course is "Incomplete" then I do need that to carry through.

 

But now you have got me thinking there may be a filter/formula/union combination somewhere to make this happen, so thanks for the thought-starter 🙂

 

I'm just thinking there is probably an elegant and simple solution somewhere in Alteryx that i don't know of, so I will need to use 15 steps instead!

 

Cheers, 

TC

grazitti_sapna
17 - Castor

Hi @TaniaC,

 

I have mocked up a workflow for your problem. I hope it helps with your problem:

unique_prob.png

Sapna Gupta
TaniaC
7 - Meteor

Oh wow - thanks for putting the work in to do that! It works perfectly - took me a while to work out how it works, but I understand now. Never could have come up with that myself - very much appreciated 🙂 

TC

Cndro_Consulting
8 - Asteroid

My thought.

Cndro_Consulting
8 - Asteroid

Can you explain what you were trying to do @grazitti_sapna 

Labels