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. 

[flying008]

Hi, @Parthasarathi 

What are you want ?  there is not used AMP.

Input				Output
Name	Group	Status		Name	Group	Status
Aaaa	A	Active		Aaaa	A	Active
Aaaa	A	Active		Bbbb	B	Inactive
Bbbb	B	Inactive		Cccc	C	Active
Bbbb	B	Inactive		Dddd	D	Active
Cccc	C	Inactive		Eeee	E	Active
Cccc	C	Active		Eeee	F	Inactive
Dddd	D	Inactive
Dddd	D	Inactive
Dddd	D	Inactive
Dddd	D	Active
Eeee	E	Active
Eeee	F	Inactive

[Parthasarathi]

@flying008,

It is not satisfying the Condition when the Name is same and the Group is different. 

For the below data, the Name is same and Group is different, in that case it should check the Active one, but instead it is picking the Inactive as well. And when using Select Sample Type to pick first one, it is going with the InActive one.

Eeee	E	Active
Eeee	F	Inactive

[ArnaldoSandoval · Accepted solution]

Hi @Parthasarathi 

Can you give mine a try? the main difference with the other workflows is that I summarize the records, which reduces duplicated records, then it sample the first record by the name field only (I previously sort the data by Name, Status).

hth

Arnaldo

[Parthasarathi]

@ArnaldoSandoval ,

It is solved with your latest workflow and by adding Group column to the sorting. Thank you for your time!

[RobertOdera]

Good morning, @Parthasarathi 

Sorry, I'm just seeing your updated use case now - yes, add Group to the sorting (ascending) and it should work.

[RobertOdera]

Also...looking at the updated solutions, @ArnaldoSandoval solution will get you what you need, right?

👉Kindly consider marking @ArnaldoSandoval as an acceptable solution to give credit 😎. 

Thank you for staying engaged and for liking all the other contributions - cheers!