Transform data

Join two inputs into one based on one or more columns from each input.

To specify the columns you want to join, click Left side columns or Right side columns.

Use the Retention type menus to specify different combinations of join. The top menu is for the first join input and the bottom one for the second join input.

• All matches: Performs a standard join.
• Singletons only: Return the first match of a one-to-many match and any matches that occur only once. This is a normal relational join with duplicates removed.
• Duplicates only: Return all the matches that are one-to-many only . This is a normal relational join excluding matches that match only once.

Click on the Venn diagram to specify the type of join you want to apply:

• Inner (center segment of the Venn): Returns all rows where the join condition is met (keys match).
• Left outer (left and center segments): Returns all rows from the left-hand input and only the rows from the right-hand input where the join condition is met (keys match).
• Left outer without intersection (left segment): A variant on the basic left outer join, which returns only rows from the left-hand input that don't meet the join condition. No rows from the right-hand input are returned.
• Right outer (center and right segments): Returns all rows from the right-hand input and only the rows from the left-hand input where the join condition is met (keys match)
• Right outer without intersection (right segment only): A variant on the basic right outer join, which returns only rows from the right-hand input that don't meet the join condition. No rows from the left-hand input are returned.
• Full outer (all segments of the Venn): Returns all rows in both the left and right inputs. If a row from one side of the join has no match in the input on the other side, the columns from the other input will contain null values.
• Full outer without intersection (left and right segments only): Returns all rows in both the left and right inputs, excluding rows where the join condition is met.
• Cross join or Cartesian product (no segments of the Venn): Returns all rows from the left input. Each row from the left input is combined with all rows from the right input.

Above the Venn diagram select More options to expose more detailed options for the join.

The Join step provides three matching options which determine how keys will be compared:

• Exact match: rows will be joined if key values on the left-hand side and right-hand side are exactly the same, including data type and casing.
• Ignore datatype: rows will be joined if keys can be standardized to the same value. A key with the alphanumeric value '1' will be joined with a key that is the integer value 1.
• Case-insensitive: rows will be joined if keys match with casing ignored. A key with value 'ABC' will be joined to a key with value 'abc'.

The Do not match nulls option configures how null values are handled in the join. When enabled, left or right column values containing nulls will never match with any other row. This means that rows where the join key contains nulls will never appear in inner join results and will always appear in left/right unmatched results if the selected join type will include left/right unmatched rows.

In the join dialog, click Show step results to view the results of the join. Specify which values from the joined columns to use in any following steps: all the matches, only duplicates or only singletons.

Click to see suggested join keys. Joins will be suggested only where column names match exact on the two side of the join. Click on the suggested join to apply it.

Filter your data using a function that returns a boolean (true or false) value. If the function returns true, the given row will be passed through to the step output.

Group and aggregate column values.

Grouping allows you to group values from one input into one or more columns and aggregate the remaining columns, if required.

Click Grouping in the step dialog then drag and drop the required columns up, into the Grouped list. Remove columns from this list by simply dragging them down. Click Apply when done.

You also configure grouped columns and use aggregate functions on the grouped values when in Show step results view: right-click on the grouped column heading and select Add aggregate. In the dialog that appears, specify the name/description for the new column, the source column to be used for aggregation and the required function.

You can drill into a particular group to see the rows that belong to it by exploring the results, selecting a particular group, right-clicking and selecting View rows in group.

Viewing group rows in other steps

The ability to drill down into the Group step results will be retained if the step is connected to any of the following subsequent steps:

• Branch
• Chart
• Export
• Filter
• Fire event
• Output
• Sample
• Sort
• Split
• Take Snapshot
• Transform
• Union

You can view the drilldown using the right-click options or the toolbar on these steps.

Interactive Datasets

The results of the grouping can be saved to a Dataset by including a Take Snapshot step in your Workflow. Furthermore, if the Interactive option is selected when creating a new Dataset on the Take Snapshot step, you can drill into the data to view the rows in each group in the Dataset.

Once an interactive Dataset has been created, it can be drilled into in various ways, such as:

• The interactive Dataset itself.
• A View with the interactive Dataset as the source.
• A Source Workflow step with the Interactive Dataset as the source.
• A Source Workflow step, with a View on the Interactive Dataset as the source.

Interactivity also works for multi batch Datasets.

Sort your data in ascending/descending order.

Click Sorting in the step, select the required columns and click Apply to save changes.

To change the sort order, click on the column in the Sorted list.

Click Show step results to view the results.