Except
The Except transformation component lets you compare two datasets (a primary table and a comparison table), and then return the rows that only exist in the primary table. Effectively, it allows you to filter out rows that exist in more than one table.
This component only compares the data in columns that appear in both tables. Otherwise, all rows would always look unique. If there are duplicate rows in the primary table, only unique rows are returned.
The component is the equivalent of writing multiple SELECT queries separated by EXCEPT.
Use case
This component is useful if you need to exclude duplicate records from further processing. For example, you might have an existing_customers dataset and a new_customers dataset. You can use the Except component to exclude any rows in your new_customers dataset that are already included in your existing customer database. This would give you an accurate overview of only your new customers.
Properties
Name = string
A human-readable name for the component.
Cast Types = boolean
Choose whether to cast types.
- Yes: If the same-named column from both inputs has differing input types, attempt to cast them to a common type. This isn't guaranteed to work. Check your data carefully before proceding with this.
- No: If the same-named column from both inputs has differing input types, report an error and halt.
Primary Table = drop-down
Select the input source to treat as the primary table. The other input source becomes the secondary (comparison) table.