The Row/Column Consolidation transformation consolidates consecutive rows or columns that are identical except for the values in one designated row or column. The values in those cells corresponding to the designated row or column are merged after the rows or columns are consolidated.
Prerequisites
The instructions in this topic assume that you have already added the transformation to your table by following the instructions in the Add a Transformation to a Table or Table Type subsection of the Table Transformations topic here.
Example
In the following example, the table has five rows. In the first two rows and the last two rows, all values are identical across every column except for the values in the 'Capacity' column.
Before
This is how the table appears before the transformation is applied. The rows with identical values are surrounded by a red box, and the different values for Capacity within these rows are similarly highlighted.
After
This is how the table appears after the transformation is applied. The first two rows are consolidated and the last two rows are consolidated. The different values for 'Capacity' are now merged into a single cell in the first row and a single cell in the last row. More information is provided in the following section on how to configure the transformation to obtain this result.
Steps
- After adding the transformation, under Parameters, click the ellipsis button (
). The Row/Column Consolidation dialog displays. Select Rows to merge rows, or select Columns to merge columns.
- If Rows is selected, the Column Type: label will display in the dialog, and the corresponding dropdown list will contain the column types that are legal for your table.
The example shown at the beginning of this topic uses the column type of 'Capacity.'
- If Columns is selected, the Row Type: label will display in the dialog, and the corresponding dropdown list will contain the row types that are legal for your table.
- In the Separator field, enter the value that should appear between the merged values in the consolidated column or row. In this example, a separator of ' & ' (an ampersand with a space on either side) is entered.
- Click OK to finish.
Error Handling
If the column type or row type that you choose appears more than once in the table, a warning will display in the dialog to ensure that you are aware that only the first column or row of this type will be affected by the transformation. To avoid confusion, it is advisable to use a dedicated column type or row type for consolidation operations.
