Intersect
The Intersect transformation component lets you compare two datasets, and then return any rows that are identical in both datasets. You can also configure this component to try to standardize the data types if these differ between your datasets.
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 an INTERSECT query.
Use case
This component is useful if you need to identify rows of data that are included in multiple datasets. For example, you can use it to:
- Find duplicate items in two inventory datasets, to improve inventory management.
- Check the consistency of data that is stored in multiple locations.
- Identify accidental duplicates—for example, invoices submitted through more than one payment system.
Properties
Name = string
A human-readable name for the component.
Cast Types = drop-down
Choose whether to cast types.
- Yes: If the same-named column from both inputs has differing input types, Designer attempts to cast them to a common type. This is not guaranteed to work. Check your data carefully.
- No: If the same-named column from both inputs has differing input types, Designer reports an error and will not continue.
Strategy
Generates multiple select queries separated by INTERSECT.