Intersect Component

Intersect Component

Compares two input flows and outputs any rows that are in common (identical) in both tables.

Because the entire row is compared, only overlapping columns are considered, otherwise no rows would look unique. Also, because this component uses the SQL INTERSECT operator, only unique rows are passed to the next component if there are duplicate rows in the source components.

Properties

Property Setting Description
Name Text The descriptive name for the component.
Cast Types Select Yes - If the same-named column from both inputs have differing input types, attempt to cast them to a common type. This is not guaranteed to work, so you should check your data carefully.
No - If the same-named column from both inputs have differing input types, report an error and do not continue.

Note: Care should be taken when casting float types into string types as this may introduce numerical inaccuracies.

Strategy

Generates multiple select queries separated by INTERSECT.

Example

In this example we have 2 tables of user data - in fact, one is an updated version of the other. When viewing the updated data, it is apparent that some users have changed their details since they were last recorded. In order to build compelling data around the most stable users, we wish to find the data of those users that have not changed their details. To do this, we will use the Intersect component. Below we show the data and its updated counterpart.

As can be seen, several users have changed their plan. If attempting to analyse this data, especially on a large scale, it might be useful to find only users that have not recently changed their data to ensure a more stable dataset.

There is little to do with the component properties. We choose to cast types because it is preferred to try and reconcile the data than to skip the record... but it is unlikely to be important since we know these data are of common types.

Finally, we can take a sample of the data output from Intersect and find the common rows.