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. Visit 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 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 displays in the Group data by these values section. 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 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. 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.

      • PercentRow: Calculates a percent based on the values.

      • PercentColumn: Calculates a percent based on the values.

      • TotalColumn: Totals all of the values.

      • TotalRow: 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 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 FieldSize 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. Visit the Data Types article for information on string data types.

In this section: