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!
Solved! Go to Solution.
@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
@StephenJPSheehan
Hope this is what you need.
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:
Thanks Emil - this solution worked perfectly!
Thanks Qiu - this solution worked great for me. I tried Emil's first which also worked, but your solution also works.