Marking Duplicates So I Can Filter Them Out

bperry30 · January 31

Good afternoon,

I am trying to mark rows that match up with each other as duplicates so that I can filter them out and only have the truly unique records left.

Example

So after I mark and filter the only record left will be the third one that doesn't have a match.

What is the best way to do this?

Thank you in advance

ColemenWilson · February 1

Ah sorry I missed that you want both the original and the duplicate row gone when a duplicate exists. Interested in your use case - why you want both records gone when there is a duplicate and not just the duplicate? Is it that the duplicate transaction is to zero out the previous? If that is the case, and `retail` is the field that is being zero'd out then I agree with @ST_-Superman-_ 's approach. Here is how I did it using the data you provided with the correct output you are looking for:

1. Overall ETL:

2. Group By step:

3. Filter Rows step

4. Output - Only unique rows

ColemenWilson · January 31

There is a tile called "Remove Duplicates" that you can use in Magic ETL to only keep unique rows.

https://www.youtube.com/watch?v=TNY86GuHl4g

bperry30 · February 1

https://community-forums.domo.com/main/discussion/comment/88081#Comment_88081

No that will leave me with one of the duplicates. I need all the pairs to be gone.

bperry30 · February 1

https://community-forums.domo.com/main/discussion/comment/88081#Comment_88081

Your solution would give me this:

I want this:

marcel_luthi · February 1

So how do you identify when something is a "duplicate"? If there is a set criteria then you can do a group by in ETL that does a count on how many times the "uniqueness" criteria shows in the dataset, then JOIN that back into the dataset and filter to leave only those were the count is 1. This if you want to go via the ETL approach. You could also go the Beast Mode approach with the FIXED function and use that as a filter, something like:

SUM(COUNT(`Date`) FIXED (BY [FIELDS THAT MAKE UP YOUR UNIQUENESS CRITERIA]))

And then filter based on this, hope one of this helps.

bperry30 · February 1

https://community-forums.domo.com/main/discussion/comment/88122#Comment_88122

Ok so in the group by I have 1. Store, Line, Item, Change Qty Abs, and Retail Abs

but what do I put in 2 and 3?

ColemenWilson · February 1

Could you explain how the two rows you screenshot above are duplicates? Is there some additional criteria beyond duplicate values in the data itself?

bperry30 · February 1

https://community-forums.domo.com/main/discussion/comment/88137#Comment_88137

It's more that they are offsets. So my way of trying to eliminate them is that they are duplicates based on Store, Line, Item, Change Qty Abs, and Retail Abs. There is much more data not shown but they are not really relevant like comments, people's usernames, and extra store information like divisions, regions, districts, etc. In total there are like 40 columns of data.

ColemenWilson · February 1

You can select which columns identify the duplicates using the remove duplicates title and selecting Store, Line, Item, Change Qty Abs, and Retail Abs as the fields that identify the duplicate.

bperry30 · February 1

https://community-forums.domo.com/main/discussion/comment/88196#Comment_88196

Again that would leave me with one of the two matching transactions and I don't want that

ST_Superman · February 1

@bperry30

Could you just SUM the Retail field by store and item and then filter your results to show items where the sum is not 0?

bperry30 · February 1

https://community-forums.domo.com/main/discussion/comment/88223#Comment_88223

Would I do that with a formula field?

ST_Superman · February 1

@bperry30

It would be a group by tile in the ETL. (Group by Store and Item and sum the Retail field)

You could probably do this with a calculated field as well. Something like:

SUM(SUM(`Retail`) FIXED (BY `Store`, `Item`))

bperry30 · February 1

https://community-forums.domo.com/main/discussion/comment/88240#Comment_88240

I can't seem to get that to work either

ColemenWilson · February 1

Ah sorry I missed that you want both the original and the duplicate row gone when a duplicate exists. Interested in your use case - why you want both records gone when there is a duplicate and not just the duplicate? Is it that the duplicate transaction is to zero out the previous? If that is the case, and `retail` is the field that is being zero'd out then I agree with @ST_-Superman-_ 's approach. Here is how I did it using the data you provided with the correct output you are looking for:

1. Overall ETL:

2. Group By step:

3. Filter Rows step

4. Output - Only unique rows

Marking Duplicates So I Can Filter Them Out

Best Answer

Answers

Categories