Filter unique records and always get first record based on two conditions

[Parthasarathi]

Hi All,

I am working with Alteryx Designer and facing issue with Filtering unique records and always get first record based on two conditions. 

I have a excel similar to the below table, I have to match the record based on the name with another excel. But based on the Group and Status column I need to pick the first row of matching, if it is Inactive then I need to select the next Active one

Name	Group	Status	Expected Record to be picked
Aaaa	A	Active	Yes
Aaaa	A	Active
Bbbb	B	Inactive	Yes
Bbbb	B	Inactive
Cccc	C	Inactive
Cccc	C	Active	Yes
Dddd	D	Inactive
Dddd	D	Inactive
Dddd	D	Inactive
Dddd	D	Active	Yes

I have tried with Select sample type, but it is failing for "Cccc", dont have idea to implement condition with Select Sample Type Component. 

[Yoshiro_Fujimori]

Hi @Parthasarathi ,

How about sorting before sampling?

Workflow

Output

[ArnaldoSandoval]

@Parthasarathi  and @Yoshiro_Fujimori 

If the AMP Engine is enabled you need the solution posted by Yoshiro, while if the AMP Engine is disabled you do not need the second sort 😮

hth

Arnaldo

[Parthasarathi]

@Yoshiro_Fujimori,

Thank you for your Quick Reply, I am reading an excel which has more than 20K records, Even  I have changed the record limit to 30K, but still few records are not coming. Hence the sorting not helping to get all group matching to a name. Still getting one "Inactive" where in the source I could see an Active record for it

[Parthasarathi]

@ArnaldoSandoval,

Yes, the AMP engine is enabled and with @Yoshiro_Fujimori's proposed solution is not working for me

[ArnaldoSandoval]

@Parthasarathi 

My solution is a bit different to the one posted by Yoshiro, as I am using a Summarise tool; Would you be able to share a sample of your Excel file, including some of the offending records (those still with unexpected result)

thanks,

Arnaldo

[Parthasarathi]

@ArnaldoSandoval 

Sample records attached, I have added a column "Expected to be picked" ignore it from your solution. These are the scenarios creating issues. 

[RobertOdera · Accepted solution]

Hi, @Parthasarathi 

Kindly consider adding the following treatment to your workflow.

I hope you find it helpful - Cheers!

[RobertOdera]

You're most welcome @Parthasarathi!

[Parthasarathi]

@RobertOdera,

It's failed in the below scenario, I think it is due to the grouping. 

Name	Group	Status	Expected Record to be picked
Aaaa	A	Active	Yes
Aaaa	A	Active
Bbbb	B	Inactive	Yes
Bbbb	B	Inactive
Cccc	C	Inactive
Cccc	C	Active	Yes
Dddd	D	Inactive
Dddd	D	Inactive
Dddd	D	Inactive
Dddd	D	Active	Yes
Eeee	E	Active	Yes	Failed here
Eeee	F	Inactive