Comparing two rows in Alteryx and Consider only one to the further steps

Question

I have a dataset with similar rows but except one column value will be different. I want to consider a row with specific value in that column and use it in the further steps.

If you see the data there are rows with duplicate ID's and names but different Status. How can I achieve this.

1. If Id is same then consider only active status.
2. If ID is different any status which is in the status column

I have tried using multirow formula but was not achieving anything. Any help will be appreciated. Thanks a lot

Answer 1

There several ways to do this. If "Active" is alphabetically the first of the possible statuses, the easiest way would be to sort on ID and Status; then run through the Unique tool and select ID there: it will uniquely select the first ID it sees, which will be the 'Active' one when there's an active case (due to the sort). If you don't want to trust "Unique" you could accomplish the same thing with the Sample tool to grab one item when grouped by ID: again due to the preceding sort, that will grab Active when it is present. (Aside: if your possible statuses are such that the sort trick won't work, simply create a new variable that will help you sort to 'Active' appearing first in a group-by-ID.)