Use the Transpose tool to pivot the orientation of the data table. It transforms the data so you may view Horizontal data fields on a vertical axis. The Transpose tool is the reverse application of the Cross Tab tool, which pivots the orientation of the data from vertical to horizontal.
This tool is useful for extracting non-conforming data. There is no limit to the number of records or fields that can be transposed.
Configure the Tool
- Key Columns: Select the columns (fields) to pivot the table around. This name remains on the Horizontal axis, with its value replicated vertically for each data field selected.
- Data Columns: Select all the columns to carry through the analysis.
- Use Select All to check all of the options, or Deselect All to clear all selections.
- Missing Columns: Select the action that you want to take if columns are missing:
- Error: Throw an error in the Results window and stop processing records.
- Warn: Throw a warning in the Results window, but continue processing records.
- Ignore: Ignore missing columns and continue processing records.
Key Field Example
The Key Field you select replicates vertically, creating a row for each Data Field you select to transpose.
For example, you have a table containing 2 rows and 4 columns. When you transpose the table and select a Key Field without deselecting any Data Fields, you will end up with 6 rows and 3 columns along with the selected Key Field, Name (represents the name of the remaining selected Data Fields), and the Value of the selected Data Fields.
If you deselect the Data Fields, the number of rows after the Transpose tool decreases.
The number of rows you will end up with after transposing the table is equal to the number of initial rows X the number of Data Fields selected, minus any columns you selected as Key Fields.