Skip to main content

Orange polygon containing a white box with vertically stacked rows followed by an arrow pointing to another box of rows placed in a horizontal row. Cross Tab Tool

One Tool Example

Cross Tab has a One Tool Example. Go to Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.

Use Cross Tab to pivot the orientation of data in a table by moving vertical data fields onto a horizontal axis and summarizing data where specified. The Cross Tab tool is the reverse application of the Transpose tool, which pivots the orientation of the data from horizontal to vertical. There is no limit to the number of rows or columns that can be passed through the Cross Tab tool.

Important

In versions before Designer 10.5, identical strings with differing capitalization were used to create separate columns with modified names. In 10.5 and later, if 2 or more cells contain the same string with different capitalization, an error is produced at runtime. To resolve this, ensure that data in each cell is unique or that the capitalization is consistent for matching data.

Configure the Tool

  1. A list of all available data values is available in the Group data by these values section. Select the values that should be used to group the data. Data with identical values is grouped together into a single row.

  2. Select a value from the Change Column Headers dropdown. A new column is created for each unique value.

  3. Select a value from the Values for New Columns dropdown. These values are used to populate the new columns.

  4. This option is only available with the AMP engine enabled. Select the Retain Special Characters in New Column Names checkbox to keep any special characters in your column/field names. When the checkbox is unchecked, Alteryx replaces special characters in column names with an underscore (_) character.

  5. Select a Method for Aggregating Values for combining multiple values in a field. Available options depend on the data type of the value selected in the Values for New Columns field.

    • Options for String data include...

      • Concatenate: Separates the values using the separator specified in the Separator field.

      • First: Displays the first found value.

      • Last: Displays the last found value.

    • Options for Number data include...

      • Sum: Sums the values.

      • Average: Calculates an average of the values.

      • Count (without Nulls): Counts the number of values excluding null values.

      • Count (with Nulls): Counts the number of values including null values.

      • Percent Row: Calculates a percent based on the values.

      • Percent Column: Calculates a percent based on the values.

      • Total Column: Totals all of the values.

      • Total Row: Adds a new row containing a total of the values.

    • When you choose one or more of the methods above, an abbreviation for that method is prepended to the column header unless Sum, First, or Last is the only method selected.

      AVG_FieldValue, SUM_FieldValue, COUNT_FieldValue.

  6. The character specified in the Separator field is automatically added between concatenated strings. This option is only available when concatenating strings.

  7. The Field Size field is the maximum field length (in characters) for concatenating strings. If the string is larger than the size specified, a warning appears in the Results window and the data is truncated. Go to Data Types for information on string data types.

In this section: