Unique
Remove repeated records, separate groups of matching rows or flag duplicates for review. Choose which columns define a match, then check the Unique and Duplicate outputs.
Find duplicate contacts
Manual Input has five contacts with name and email columns. Ada's ada@example.com entry appears twice, and a third Ada row uses ada.work@example.com. Unique starts in First mode with both columns selected for matching.
Unique interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| name | |
|---|---|
| Ada | ada@example.com |
| Lin | lin@example.com |
| Ada | ada.work@example.com |
| Sam | sam@example.com |
- The Unique output has four rows. Select the Duplicate output port on the block to see the repeated Ada, ada@example.com row, then select the Unique output port to go back.
- Clear Match for
email. Onlynamenow defines a match, so the Unique output has Ada, Lin and Sam. Both later Ada rows go to Duplicate, including the one with a different email. - Choose
Splitin Mode. Unique now has only Lin and Sam. Select the Duplicate output port to see all three Ada rows, including the first one. - Choose
Flagin Mode, then select the Unique output port. All five rows appear withis_duplicate,occurrenceandkey_countcolumns.
When to use
Use Unique to find or remove repeated records, such as the same contact entered twice. To count rows per group instead, use Summarize.
Read the two outputs
With First selected and both columns checked, the Unique output has Ada (ada@example.com), Lin, Ada (ada.work@example.com) and Sam, in input order. The Duplicate output has the second Ada row with ada@example.com.
The different email makes Ada's work contact a separate match when both columns are checked. If you clear Match for email, all three Ada rows belong to the same group. First keeps the earliest Ada row on Unique and sends the other two to Duplicate. The email column stays in both outputs.
Split treats the whole repeated group as duplicates. With only name checked, all three Ada rows go to Duplicate, while Lin and Sam go to Unique.
Read the flags
Flag keeps every input row on the Unique output and adds three columns:
is_duplicateis false for the first row of a match and true for every later one.occurrencenumbers rows within each matching group, starting at 1, in input order.key_countis the total number of rows in that matching group.
With only name checked, Ada's rows have occurrences 1, 2 and 3, each with a key_count of 3. The first Ada row has is_duplicate set to false; the later two are true. Lin and Sam each have occurrence 1, key_count 1 and is_duplicate false.
The Duplicate output is empty in Flag mode. The source table doesn't change in any mode.
To combine matching rows into totals or other grouped calculations, use Summarize.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
2 ports- Unique
- Duplicate
Configure the block
Choose how to handle matching rows, then select the columns that define a match in Match On. If a chosen column is missing from the input, the block reports an error. New columns aren't added to a selection you set yourself.
- Mode
First keeps the first row of each matching group on Unique and sends later matches to Duplicate. Split sends rows with no matches to Unique and all rows in repeated groups to Duplicate. Flag sends all rows to Unique with three added columns, is_duplicate, occurrence and key_count, and leaves Duplicate empty. Default: First.
- Column
Identifies an input column in the Match On grid.
- Match
Select to include the column when comparing rows. All selected values must match for rows to belong to the same group. Clearing a checkbox doesn't remove that column from the output. At least one must remain selected. Default: true.
- Type
Shows the input column's data type. This is a read-only label; Unique doesn't convert values.
Unique FAQs
- How do I remove duplicates or dedupe rows?
- Use First and check Match for the columns that define a duplicate. Check every column to keep distinct rows. The Unique output has the first row of each match, and the Duplicate output has the rest.
- Does First choose the most complete record?
- No. It keeps the first row in input order, even if a later match has more information. If the input order changes, the row it keeps can change too.
- Do missing values match?
- Yes. NULL values in a selected column are grouped together. The other selected columns must also match for the rows to belong to the same group.
- Does Unique merge values from matching rows?
- No. It routes whole rows or adds flags. It doesn't fill missing cells from another row or combine different emails into one record.
- Can I clear every Match checkbox?
- No. Unique needs at least one match column. Clearing every checkbox gives an error.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.