Documentation

    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.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    nameemail
    Adaada@example.com
    Linlin@example.com
    Adaada.work@example.com
    Samsam@example.com
    1. 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.
    2. Clear Match for email. Only name now 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.
    3. Choose Split in Mode. Unique now has only Lin and Sam. Select the Duplicate output port to see all three Ada rows, including the first one.
    4. Choose Flag in Mode, then select the Unique output port. All five rows appear with is_duplicate, occurrence and key_count columns.

    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_duplicate is false for the first row of a match and true for every later one.
    • occurrence numbers rows within each matching group, starting at 1, in input order.
    • key_count is 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.