Marking Duplicates

Q&A
, ,
1

I have a dataset where IDs (POs) can be duplicates, but I don’t have any other fields to differentiate between the duplicate rows. My goal is to mark rows with the same PO as “Duplicate” and the rest as “Unique.”

I’ve tried using countOver and distinctCountOver, but it’s not giving me the desired result.

Here’s an example of the dataset I’m working with:

Manufacturer PO Carrier Status Duplicate Appt
Manufacturer A A1B2C3D4 Carrier 1 No Appt Duplicate
Manufacturer B E5F6G7H8 Carrier 2 No Appt Duplicate
Manufacturer C A1B2C3D4 Carrier 1 No Appt Duplicate
Manufacturer D I9J1K2L3 Carrier 3 Late Unique
Manufacturer E M4N5O6P7 Carrier 4 Late Unique
Manufacturer F Y7Z8A1B2 Carrier 5 No Appt Unique
Manufacturer G K2L3M4N5 Carrier 6 Compliant Unique
Manufacturer H E5F6G7H8 Carrier 2 No Appt Duplicate
Manufacturer I Q8R9S1T2 Carrier 7 Late Unique

What formula or approach can I use to properly mark rows as “Duplicate” if they share the same PO and “Unique” otherwise?

2

Hi @mcskylar,

Can you try this?

PO Count = countOver(PO, [], PRE_AGG) 

Duplicate Appt = ifelse({PO Count} = 1, 'Unique', 'Duplicate')
3

Hi @mcskylar

Welcome to the community!

To mark the rows in your dataaset as either “Duplicate” or “Unique” based on the PO column, you can use a combination of countOver() and a conditional logic function to flag the rows. The goal is to count how many times each PO appears and then classify rows as “Duplicate” if the count is more than 1, or “Unique” if the count is 1.

Create a conditional calculated Field for `Duplicate/Unique, you can create calculated field to mark whether the row is a “Duplicate” or “Unique”.

Example:
ifelse(countOver(PO, [PO], PRE_AGG) > 1, 'Duplicate', 'Unique')

This formula checks if the count of the PO is greater than 1. If it is, it marks the row as “Duplicate”; otherwise, it marks it as “Unique.”

4

I’ve tried that but still the POs that are duplicate aren’t marked and unique POs get marked as duplicates

5

I’ve used all of these:

/ifelse(distinctCountOver({PO ID}, [{PO ID}], PRE_AGG) > 1, “Duplicate”, “Unique”)/

/ifelse(countOver({Vendor Code}, [{PO ID}], PRE_AGG) > 1, ‘Duplicate’, ‘Unique’)/

ifelse(countOver({PO ID}, [{PO ID}], PRE_AGG) = 1, ‘Duplicate’, ‘Unique’)

6

Hi @mcskylar

I tried using the example dataset you shared, and the following are the outputs.

image

image

If you can recreate this issue in Arena using sample non-sensitive data, I can take a deeper look to see what is going on.

7

Hi @mcskylar,
It’s been awhile since we last heard from you; did you have any additional questions regarding your initial topic or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

8

Hi @mcskylar,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!