Applying Function Transformation

A function transformation applies functions of string, numeric, conversion, and datetime to define an attribute of a logical entity, or one of the parameters of a parent function transformation.

  1. Click the Browse tab on the Entity Transformation wizard.
  2. From the Attribute List, select the required attribute.
  3. Select the Function transformation type option.
  4. From the Category drop-down, select one of these function transformations, as needed:
    • String function transformations, such as concatenation of two string columns and left or right trim.
    • Numeric function transformations, such as obtaining absolute value of a number from a column and returning an ASCII or Unicode integer representation of a character.
    • Conversion function transformation, such as convert or parse data types
    • Datetime function transformations
      To apply a String function transformation:
      In the Category field, select String.
      Select the Name of the required string transformation.
      Click the string arrow, and use Column, Constant, or Function option to define the parameters of the string function. For example, if you selected lcase(string string) as the function Name, select the string that needs to be transformed to lower case.
      Note: In selecting function Name, take care that the Return type of the function matches the data type of the column you selected for transformation. The Return type is displayed below the Name field after you select the function name.
      To apply a Numeric function transformation:
      In the Category field, select Numeric.
      Select the Name of the required numeric transformation.
      Click the number arrow, and use Column, Constant, or Function option to define the parameters of the numeric function. For example, if you selected abs(integer number) as the function Name, select the integer that needs to be transformed to its absolute value.
      Note: In selecting function Name, take care that the Return type of the function matches the data type of the column you selected for transformation. The Return type is displayed below the Name field after you select the function name.
      To apply a Conversion function transformation:
      In the Category field, select Conversion.
      Select the Name of the required conversion transformation. Options to specify the value to be converted and the targeted output format is displayed below the Name field. For example, if you select parsedouble(string double, string format) as the function Name, double and format options are displayed to specify the value and format respectively.
      Click double and use Column, Constant, or Function option to define the selected conversion function parameters.
      Note: In selecting function Name, take care that the Return type of the function matches the data type of the column you selected for transformation. The Return type is displayed below the Name field after you select the function name.
      Select the format to which the source value needs to be converted using one of these Target options: Column, Constant, or Function. For more information, see Supported Datatypes for Conversion Transformations
      Note: The format conforms to the standard scheme as specified in Java Decimal formats. See this table for examples of Type and Value in case you choose Constant option for format specification:
      Table 1. DecimalFormat.java Output
      Value Pattern Output Description
      123456.789 ###,###.### 123,456.789 The pound sign (#) denotes a digit, the comma is a placeholder for the grouping separator, and the period is a placeholder for the decimal separator.
      123456.789 ###.## 123456.79 The value has three digits to the right of the decimal point, but the pattern has only two. The format method handles this by rounding up.
      123.78 000000.000 000123.780 The pattern specifies leading and trailing zeros, because the 0 character is used instead of the pound sign (#).
      12345.67 $###,###.### $12,345.67 The first character in the pattern is the dollar sign ($). Note that it immediately precedes the leftmost digit in the formatted output.
      Note: See more information about decimal formats here: https://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html and details of special pattern characters here https://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html.
  5. Click OK.
    The applied function transformation is reflected below the attribute.
    Note: To delete the mapping, click corresponding to it. To cancel the updates made to this pop-up, click the Cancel button.
  6. Click OK.
    The mappings are displayed on the Create Mapping page.
  7. To save details, click Save on the Create Mapping page.

Example 1- Applying a String Function Transformation

For example, if you wish to trim spaces from the right of each value of the column ItemName of the physical model table demo_Customers1 and fill in the trimmed value into the linked Name column of the logical table Product:
  1. Select the Product table on the canvas.
  2. In the Transformations panel, click adjacent to the Name column, and in the Transformation of column 'NAME' pop-up window, select the Function option.
  3. In the Category drop-down list, select String.
  4. In the Name drop-down list, select the rtrim(string string) function.
  5. The String function gets added to the pop-up below the entry fields. Click this function, and define the parameter on which this function needs to be applied by using the Column option such that the resulting data type of the parameter is a string.
  6. Click OK.
The applied function transformation is depicted on the canvas as below:

Example 2: Applying Numeric function transformation.
  1. Select the productid column on Product table on the Create Mapping page.
  2. In the Transformations panel, click adjacent to the productid column, and in the Transformation of column productid (integer) pop-up window, select the Function option.
  3. In the Category drop-down list, select Numeric.
  4. In the Name drop-down list, select abs(integer number). Option to specify the required number is displayed below the Name field.
  5. Click the number arrow, and use the column option to select CustID (Integer) column of customer_car table in the FramworkDB2 physical model.
  6. Click OK.

    This mapping is displayed on the canvas.