Alteryx Cross Tab tool > Prophecy Pivot gem

The Alteryx Cross Tab tool reshapes data by converting row values into columns, typically aggregating values in the process. It is commonly used to create summary-style tables for reporting and analysis. In Prophecy, pivot operations are implemented using SQL-style pivot transformations. These transformations require an aggregation step, even when the input data has only one value per group. In Prophecy, the same outcome is implemented using the Pivot gem, which performs SQL-backed pivot operations over grouped data.

Automated migration results

When Import detects an Alteryx Cross Tab tool, it generates a Pivot gem configured to match the Cross Tab settings:

Group-by fields in Alteryx are mapped to the Pivot gem’s Row Grouping Settings.
The selected Column Headers field in Alteryx is used as the pivot column.
The Values for New Columns field is used as the pivot value.
The selected aggregation method (for example, Sum, Count, Min, Max, First, or Last) is mapped to the Pivot gem’s aggregation function.
- In BigQuery environments, the First and Last aggregation methods are not supported directly during pivot operations. When these options are detected, Import generates a Reformat gem to compute the FIRST/LAST logic before applying the Pivot transformation.
Output column names are derived from the pivoted values.

The resulting dataset has one row per group and one column per distinct pivot value, matching the Cross Tab output shape.

Manually replicate in Prophecy

To reproduce Cross Tab behavior manually:

Add a Pivot gem to the canvas.
Configure the Group by columns (these define the output row grain).
Select the column whose values should become output columns.
Select the column containing values to aggregate.
Choose an appropriate aggregation function.

Configuration options

In Alteryx (Cross Tab tool)

Configure the tool by selecting:

Group data by these values (row identifiers)
New column headers (field whose values become column names)
Values for new columns (field to aggregate)
Aggregation method (Sum, Count, Min, Max, First, etc.)
Optional: handling of nulls and missing combinations

In Prophecy (Pivot gem)

Configure the Pivot gem by specifying:

Row Grouping Settings (corresponding to Alteryx group-by fields)
Pivot Column Settings (values become output column names)
Value for New Columns (values to aggregate)

The Pivot gem expresses this logic as a SQL pivot under the hood, but exposes configuration through a low-code interface.

When importing workflows for BigQuery, the Cross Tab aggregation options First and Last are implemented using a Reformat gem before the Pivot operation. This ensures that the correct value is selected prior to reshaping the dataset.

Output behavior

Alteryx outputs one row per group, with new columns created from distinct values in the pivot field.
Prophecy outputs the same wide, pivoted structure, with one column per pivot value and aggregated results in each cell.

If a particular group does not contain a given pivot value, the corresponding output cell is null unless otherwise configured.

Known caveats

Dynamic columns: Both tools generate output columns based on data values. Changes in the underlying data can alter the output schema.
Aggregation required: Pivoting inherently involves aggregation; non-aggregated values must be reduced using an aggregation function.
Wide outputs: Large numbers of pivot values can result in very wide schemas, which may impact readability or downstream processing.
Null handling: Missing combinations typically produce nulls and may require downstream handling.
First/Last aggregation in BigQuery: Because BigQuery does not support FIRST or LAST directly within pivot operations, in BigQuery contexts, Import introduces a Reformat gem to compute the correct value before pivoting.

Example

Goal: Create one row per order_id, with separate columns for each category, summing amount.

Input dataset

order_id	category	amount
1	A	10
1	B	5
2	A	7
2	B	3