> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Alteryx Summarize Tool > Prophecy Aggregate, Reformat, and Deduplicate Gems

> Mapping between Alteryx's Summarize tool and equivalent aggregation patterns in Prophecy

The Alteryx Summarize tool performs grouped and non-grouped aggregations by applying one or more “actions” (such as `group by`, `sum`, `count`, `median`, `concatenate`, `first`, `last`, and spatial summarizations) to selected fields, producing a summarized output table whose columns are defined by each action’s Output Field Name.

When Import encounters an Alteryx Summarize tool, it analyzes the selected actions and converts the Summarize tool into either an [Aggregate](/data-analysis/gems/transform/aggregate), a [Deduplicate gem](/data-analysis/gems/prepare/deduplicate), or a [Reformat gem](/data-analysis/gems/prepare/reformat).

## Automated migration results

When Prophecy imports a Summarize tool, it applies the following logic:

* Summarize tools that include grouping and aggregation actions are migrated to an Aggregate gem.
* Summarize tools that include grouping without additional aggregation are migrated to a Deduplicate gem.
* Summarize tools that include non-grouped aggregation or positional actions are typically migrated to a Reformat gem.

Some Summarize configurations—especially those combining grouped and non-grouped actions—may be transpiled into multiple Prophecy gems.

## Manually replicate in Prophecy

When recreating an Alteryx Summarize step manually in Prophecy, you do not need to mirror the internal structure generated by Import. Instead, choose the Prophecy gem that best matches the *intent* of the Summarize action.

As a general guideline:

* Use the **Aggregate** gem when your goal is to reduce rows using grouping and aggregation (for example, sums, counts, medians, percentiles, or spatial aggregations).
* Use the **Deduplicate** gem when your goal is to retain one row per unique key without computing additional metrics.
* Use the **Reformat** gem for non-grouped aggregations or positional summarizations that collapse the dataset to a single row.

When configuring expressions manually, pay special attention to how Alteryx treats blanks, nulls, and data types. See [Databricks Data Types](/import-tool/alteryx/alteryx-to-prophecy-sql/data-type-mapping) for more information.

## Configuration options

### Alteryx Summarize tool

In Alteryx, the Summarize tool is configured by selecting one or more **actions** for individual fields. Each action defines both the operation to perform (for example, *group by*, *sum*, *count*, *concatenate*, *first*, *median*, or spatial and financial summarizations) and the resulting **Output Field Name**.

Multiple actions can be applied to the same field, and actions may be combined across fields. The presence or absence of *group by* actions determines whether the output is grouped (one row per unique key) or non-grouped (a single summary row).

Alteryx also applies implicit semantics for blanks, nulls, ordering, and data types, which influence how summarization results are computed.

### Prophecy gems

In Prophecy, Summarize behavior is represented using one of several gems depending on the intent of the Alteryx configuration:

* Use the **Aggregate** gem to apply aggregations to grouped rows. You configure **Group by columns** in the gem, and target aggregations to specific output columns.
* Use the **Deduplicate** gem to group rows without aggregations. One row is retained per unique key defined by the grouped columns.
* Use the **Reformat** gem for non-grouped summarizations and positional actions that collapse the dataset to a single row.

## Output behavior

Summarize actions typically reduce the number of rows in the dataset. The resulting output contains:

* One row per unique group when grouping is specified
* A single row when no grouping is specified

Column order and naming generally follow the order and names defined in Alteryx, though internal expression expansion may affect how the logic is represented under the hood.

For certain actions (such as positional or distribution-based summarizations), result values may depend on engine-specific behavior.

## Known caveats

* **Explicit expressions**. Import preserves Alteryx Output Field Names as Prophecy output columns whenever possible. To match Alteryx semantics, especially around blank and null handling, Import may generate more explicit expressions than a user would typically write by hand (for example, conditional logic around `count`, string aggregation, or spatial functions).
* **Ordering-dependent actions**. Summarize actions such as *first* and *last* depend on record ordering. In Databricks SQL, ordering is not guaranteed unless explicitly defined, so results may differ from Alteryx when input order is significant.
* **Blank vs null semantics**. Alteryx distinguishes between blank and null values. Databricks SQL does not make the same distinction by default, so imported expressions may include explicit logic to preserve Alteryx behavior.
* **Statistical functions**. Some statistical summarizations (such as percentiles, variance, or mode) rely on Databricks SQL’s native implementations. Minor differences in definitions or tie-breaking behavior may occur.
* **Financial calculations**. Financial Summarize actions (such as `npv` or `irr`) are not supported directly and will require manual adjusting after import.

## Examples

An input dataset contains sales records with the following fields:

* `Region`
* `OrderDate`
* `OrderAmount`
* `OrderID`

### Example: Grouped aggregation with multiple actions

Goal: Produce a region-level summary table with one row per region that reports total sales, number of orders , and the most recent order date.

#### Alteryx (Summarize tool)

In Alteryx, configure a Summarize tool with these actions:

* **Group By**: `Region`
* **Sum**: `OrderAmount` (Output Field Name: `TotalSales`)
* **Count**: `OrderID` (Output Field Name: `OrderCount`)
* **Max**: `OrderDate` (Output Field Name: `LastOrderDate`)

The resulting output contains one row per `Region`, with total sales, number of orders, and the most recent order date.

#### Prophecy (Aggregate gem)

To reproduce this logic manually in Prophecy:

1. Add an **Aggregate** gem to the canvas.
2. Under **Group By Columns**, select `Region`.
3. Under **Expressions**, use the Visual Builder to add the following columns:

   * Target column `TotalSales` configured as a `sum` function with argument `OrderAmount`.
   * Target column `OrderCount` configured as a `count` function with argument `OrderID`.
   * Target column `LastOrderDate` configured as a `max` function with argument `OrderDate`.
   * Target column `Region` configured as `Region`.

### Example: Grouping without aggregation

Goal: Reduce the dataset to one row per unique order id.

#### Alteryx (Summarize tool)

Configure the Summarize tool with a single action:

**Group By**: `OrderID`

#### Prophecy (Deduplicate gem)

1. Add a **Deduplicate** gem to the canvas.
2. Select **Distinct Rows**.
3. Add `OrderID` as a target column, configured as `OrderID`.

### Example: Non-grouped summarization

Goal: Collapse the entire dataset into a single-row summary containing overall totals.

#### Alteryx (Summarize tool)

Configure the Summarize tool to include no Group By actions and:

* **Sum**: `OrderAmount` (Output Field Name: `TotalSales`)
* **Count**: `OrderID` (Output Field Name: `OrderCount`)

#### Prophecy (Reformat gem)

Use a **Reformat** gem:

1. Add a Reformat gem with no grouping.
2. Under **Expressions**, use the Visual Builder to add the following columns:
   * Target column `TotalSales` configured as a `sum` function with argument `OrderAmount`.
   * Target column `OrderCount` configured as a `count` function with argument `OrderID`.