Cross Tab tool icon

Cross Tab Tool

Last modified: September 15, 2021

Docs are available before the release of Designer Cloud so you can get a sneak peek. This content might change between now and the official release.

One Tool Example

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

Use Cross Tab to pivot the orientation of data in a table by moving vertical columns 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 you can pass through Cross Tab.

If 2 or more cells contain the same string with different capitalization, a number is appended to the string name after the 1st instance. For example, ColumnID and columnId become ColumnID and columnId2.

Configure the Tool

  1. Select a column from Create New Column From. A new column is created for each unique value.
  2. Select a column from Populate with Values From. The values from this column are used to populate the new columns.
  3. Select an  Output Method for combining multiple values in a column. Available options depend on the data type of the column selected in Populate with Values From.
    • Options for string data include...
      • Concatenate Values: Separates the values using the separator specified in the Separator field.
      • Use 1st Value: Displays the 1st value found.
      • Use Last Value: Displays the last value found.
      • The character specified in the Separator field is automatically added between concatenated strings. This option is only available when concatenating strings.
      • The Character Length is the max 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. Visit the Data Types article for information on string data types.
    • 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.
      • Use 1st Value: Displays the first found value.
      • Use Last Value: Displays the last found value.
  • When you choose 1 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. For example, AVG_FieldValue, SUM_FieldValue, and COUNT_FieldValue.

Output limitations for certain data types

For these data types you can only Use 1st Value or Use Last Value:

  • SpatialObj
  • BLOB
  • Date
  • Time
  • DateTime
  • Bool

 

Was This Page Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support. Can't submit this form? Email us.