Filter out duplicate records with a blank value unless it's the only record

Hello,

I'm looking to filter out records in my data that have a blank value in one column "Name", but only if the record is a duplicate the "RecordID" field. For example:

RecordID	Name	Value
5	10	5040000
61	2	112370815
61	3	112766510
61	4	17653044
61	5	8305757
61	6	485695
61	7	21201225
61	8	182425
1
5
61

In the above extract, I would want to keep the first record for where "RecordID" = 5 ("Name" = 10) and remove the blank record where "RecordID" = 5. However, for "RecordID" = 61, I want to keep all records except for the last blank record. Finally, there is only one record for "RecordID" = 1, and I want this to be kept and not removed.

Appreciate any advice you may have with helping solve this problem!

Help

Preparation

Accepted answers

Emil_Kos

Hi @StephenJPSheehan,

I have prepared a workflow for you:

To explain a little bit first I used summary tool + join tool in order to add information about how many times this record ID occurred in the data.

Second and most important is the multi row tool:

IIF([RecordID]=[Row-1:RecordID]
AND [CountDistinct_Name]>1
AND IsEmpty([Name])
, 1, 0)

I am checking if the previous record ID is exactly the same. At the same time, I want to check if this isn't the unique value and if the column empty isn't empty.

Thanks to this formula I am able to add a flag that will allow me to remove unnecessary data.

My final output:

Workflow Filter.yxmd

All comments

joshuaburkhow

@StephenJPSheehan This is a good one! Haven't seen a question like this in a while 😉

I would probably go down the route of sorting by id and name then running a group Record ID (https://gallery.alteryx.com/?ref=downloads#!app/CReW-GroupByRecordID/5e417bbf0462d70decb763a9) to get the counts of each and then simply a filter to say only keep '1's. That seems like it should do it

Qiu

@StephenJPSheehan
Hope this is what you need.

1230-StephenJPSheehan-1.yxmd

Emil_Kos

Hi @StephenJPSheehan,

I have prepared a workflow for you:

To explain a little bit first I used summary tool + join tool in order to add information about how many times this record ID occurred in the data.

Second and most important is the multi row tool:

IIF([RecordID]=[Row-1:RecordID]
AND [CountDistinct_Name]>1
AND IsEmpty([Name])
, 1, 0)

I am checking if the previous record ID is exactly the same. At the same time, I want to check if this isn't the unique value and if the column empty isn't empty.

Thanks to this formula I am able to add a flag that will allow me to remove unnecessary data.

My final output:

Workflow Filter.yxmd

StephenJPSheehan

Thanks Emil - this solution worked perfectly!

StephenJPSheehan

Thanks Qiu - this solution worked great for me. I tried Emil's first which also worked, but your solution also works.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703