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 NameGroupStatusExpected Record to be pickedAaaaAActiveYesAaaaAActive BbbbBInactiveYesBbbbBInactive CcccCInactive CcccCActiveYesDdddDInactive DdddDInactive DdddDInactive DdddDActiveYes I have tried with Select sample type, but it is failing for "Cccc", dont have idea to implement condition with Select Sample Type Component.

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

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.

Developer

Accepted answers

RobertOdera

Hi, @Parthasarathi

Kindly consider adding the following treatment to your workflow.

I hope you find it helpful - Cheers!

For_parthasarathi.yxzp

ArnaldoSandoval

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

2023_04_23_Filter_Unique_Records-01.yxmd

All comments

Yoshiro_Fujimori

Hi @Parthasarathi ,

How about sorting before sampling?

Workflow

Output

FilterUnique.yxmd

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.

Sample.xlsx

RobertOdera

Hi, @Parthasarathi

Kindly consider adding the following treatment to your workflow.

I hope you find it helpful - Cheers!

For_parthasarathi.yxzp

RobertOdera

You're most welcome @Parthasarathi!

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

Hi @Parthasarathi

hth

Arnaldo

2023_04_23_Filter_Unique_Records-01.yxmd

Parthasarathi

@ArnaldoSandoval ,

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703