Cross Tab Tool

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

Similarly, you can use the Transpose Tool to pivot the orientation of the data from horizontal to vertical.

Version support

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 two or more cells contain the same string with different capitalization, an error will be 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

This tool will transform your dataset. If you want to preserve the order of your rows, you can first add a Record ID Tool to your workflow before adding the Cross Tab tool.

To configure the transformation of your data:

  1. Which columns do you want to group by? In Group data by these values, select the values that should be used to group the data. Data with identical values are grouped together into a single row.
  2. Which column contains values representing your new column headers? Select a value from Change column headers. Alteryx will create a new column for each unique value that it finds in the column you select.
  3. Which column has the values you want to use to populate your newly created columns? Select a value from Values for new columns to populate the new columns.

  4. Alteryx will combine values together when multiple values end up belonging to the same column and row. How do you want to aggregate or combine values? Select a Method for aggregating values for combining multiple values in a column. Available options depend on the data type of the value selected in Values for new columns. See Data Types.

    Options for String data include:

    • Concatenate: Separates the values using the separator specified in Separator.
    • First: Displays the first found value.
    • Last: Displays the last found value.

    Options for Numerical 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.
    • First: Displays the first found value.
    • Last: Displays the last found value.

    When choosing one or more of the methods above, an abbreviation for that method will be added to the column header name, unless SUM is the only method selected.

    AVG_ColumnValue, SUM_ColumnValue, COUNT_ColumnValue.
  5. The character you specify in Separator is automatically added between concatenated strings. This option is only available when concatenating strings.
  6. The Field Size field is the maximum field length for concatenating strings. If the string is larger than the size specified, Alteryx displays a warning in the Results window and truncates the data.

The Cross Tab tool pivots 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.

Version support

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 two or more cells contain the same string with different capitalization, an error will be 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 display in the Group Data by these Values field. Select the values that should be used to group the data. Data with identical values are grouped together into a single row.
  2. Select a value from the New Column Headers drop-down list. A new column is created for each unique value.
  3. Select a value from the Values for New Columns drop-down list. These values are used to populate the new columns.

  4. 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. See Data Types.

    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 choosing one or more of the methods above, an abbreviation for that method will be prepended to the column header, unless SUM, First, or Last is the only method selected.

    AVG_FieldValue, SUM_FieldValue, COUNT_FieldValue.
  5. The character specified in the Separator field is automatically added between concatenated strings. This option is only available when concatenating strings.
  6. The Field Size field is the maximum field length for concatenating strings. If the string is larger than the size specified, a warning will appear in the Results window and the data will be truncated.