Three cell text formatting transformations are available that can sort values, consolidate ranges, and remove duplicate values within table cells. These are:
- Sort values within cells
- Range consolidation within cells
- Remove duplicate values within cells
Note: Though these text transformations also appear in the list of transformations available for attribute transformations created in System Setup, they are only used to transform content within table cells. As such, they serve no purpose for attribute values that display outside of tables. For more information on other attribute transformations and what they do, refer to the Transformations topic in the Resource Materials online help here.
Sort values within cells
The Sort values within cells text transformation is used to sort multiple values when they are contained within a single table cell. It is available as a selection under 'Add Transformation' within the Attribute Formatting and Row/Column Text Formatting table transformations, as shown below:
Entries within a cell may be sorted alphabetically, numerically, or by fractional values, and in ascending or descending order. Since the sorting affects multiple values within a single cell, a separator (value delimiter) must be placed between the values to differentiate them. If a hard return should separate the entries in a cell, the delimiter of \n should be used.
Examples
The following examples use the pipe character (|) as the delimiter:
- The value string '2002|2007|2005|2012' becomes '2002|2005|2007|2012' when sorted using the Ascending and Numeric options.
- 'A|C|H|B|G ' becomes 'A|B|C|G|H' when sorted using the Ascending and Alphabetic options.
- 'DC7|DC9|DC13|DC12|DC5|DC6' becomes 'DC5|DC6|DC7|DC9|DC12|DC13' when sorted using the Ascending and Numeric options.
The following example shows a table in which this transformation will be applied to sort values in ascending alphabetic order using the '/ ' delimiter. As shown in the field with the ellipsis button, the transformation has been selected to apply to cells within the 'Engine Series' column.
Before the transformation is applied, the values in the Engine Series column (column 5) display in non-alphabetical order, e.g., AL / AJ / AG / AH / AM / AK / AU.
After the transformation is applied, the values display in alphabetical order, e.g., AG / AH / AJ / AK / AL / AM / AU.
Remove duplicate values within cells
The Remove duplicate values within cells text transformation is used to remove duplicate values when they are contained within a single cell of a table. Like the 'Sort values within cells' transformation, it is available as a selection under 'Add Transformation' within the Attribute Formatting and Row/Column Text Formatting table transformations, as shown below:
This transformation is useful when consecutive rows in a table have been consolidated into one row. When this is done, the resultant row will often have cell entries of merged data that require some cleanup and/or consolidation.
Duplicates are removed by specifying a value delimiter. For example, using the pipe character (|) as the delimiter, the string 'DC9|DC9|DC7|DC12|DC9|DC7' becomes 'DC9|DC7|DC12'. If a hard return separates the entries in a cell, the delimiter of \n should be specified.
Example
The following example shows a table with two adjacent rows that contain identical information except for in one column (Engine series, column 5).
After a Row Consolidation transformation is applied, the value 'ED' appears twice in the Engine Series column.
To remove this duplicate value, the 'Remove duplicate values within cells' transformation is applied as part of the Row/Column Text Formatting transformation. It is configured to remove values from the Engine Series column that are separated by the ' / ' delimiter.
After the transformation is applied, the duplicate 'ED' values are removed and consolidated into a single value.
Range consolidation within cells
The Range consolidation within cells text transformation is used to consolidate ranges of data that might exist within a single cell of a table. Like the 'Sort values within cells' and 'Remove duplicate values within cells' transformations, it is available as a selection under 'Add Transformation' within the Attribute Formatting and Row/Column Text Formatting table transformations, as shown below:
As part of the consolidation process, the transformation allows users to provide delimiter characters to be used between values, as follows:
- Value Delimiter: Users may enter one or more characters to indicate the delimiter between the entries in a cell. If a hard return separates the entries in a cell, the delimiter of \n should be used.
- Range Delimiter: Users may enter one or more characters that denote the delimiter. This delimiter is used when ranges are created as the result of this operation. A range can be created that is ascending (low-to-high) or descending (high-to-low). Example: 2007-2012 or 2012-2007 if a numerical range, and A-F or F-A for an alphabetic range.
The following examples assume the value delimiter is the pipe character (|) and the range delimiter is a hyphen (-):
- The string '2002|2003|2004|2005|2006|2007' becomes '2002-2007'
- '2015|2014|2013|2012' becomes '2015-2012'
- 'A|B|C|F|G|H' becomes 'A-C|F-H'
- '2002|2003|2004|2005|2007|2008' becomes '2002-2005|2007-2008'
Note: This transformation only operates on numbers or single characters in the ASCII range 'A to Z' or 'a to z.'
Example
The following example shows a table in which a cell contains a range of values after a Row Consolidation transformation has been applied. The values are both unsorted and separated by '\\ ' as a delimiter.
To sort these values and convert them into a range, two transformations are applied. The Sort values within cells text transformation is first applied to sort the numbers in ascending numeric order. Next, Range consolidation within cells is applied, which has been configured to remove the \\ value delimiters and replace them with 'to.'
After the transformations are applied, the values are consolidated into a range.
