Removing duplicates

jrtomici

Hi, I need help coming up with logic for this problem. I have a dataset that looks like this:

ID      Source    
1       A         
1       A         
1       B         
1       B         
2       A         
3       B         

I want to only remove rows from source A IF that same row is also in source B. Otherwise I want to keep rows unique to source A and unique to source B. The output should look like this:

ID      Source    
1       B         
1       B         
2       A         
3       B         

Thank you

ColemenWilson

@jrtomici Yes I re-read your question and realized that my original solution would not work. What I would recommend is self joining the data on itself for every record in A. This way you can compare each row of data in A with every row in B and look for matches. This will temporarily explode your data, but you can then filter it down before the output.

ColemenWilson

Using the remove duplicates tile in magic etl you can choose what fields make a row unique. So in your case you would select just ID and Name to search for the duplicates.

EDIT: I re-read your question and the above solution would not get you what you are looking for. You would need to self join the data with itself so that the data can be compared to itself row by row. This will temporarili yexplode your data but hen you can filter it down before the final output.

MichelleH

@jrtomici If you are using MagicETL, try using a Group By tile to condense your table. Set ID and Name as your columns to group by, and add a formula grouping for MAX(Source). Note that this will keep the source that is last alphabetically since your example includes "A" and "B" as your values, but you may need to use MIN instead if the value you want to keep is first alphabetically.

jrtomici

@ColemenWilson Ugh I apologize — what makes this complicated is that the Name field is not unique. So there is no unique identifier for each row across both sources.

ColemenWilson

@jrtomici Yes I re-read your question and realized that my original solution would not work. What I would recommend is self joining the data on itself for every record in A. This way you can compare each row of data in A with every row in B and look for matches. This will temporarily explode your data, but you can then filter it down before the output.

pauljames

@jrtomici , have you thought about using rank and window tile to create unique identifiers as first step? Then applying remove duplicate logic?