Applying a Function Transformation - discovery

A function transformation allows you to apply 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.

On the Create Mapping <logical model name> page, select the entity corresponding to the attribute to which you want the transformed value applied, and click the Transformations collapsible tab on the right side of the page.
The Transformations panel is displayed showing the name of all the attributes in the selected entity. The Transformation column remains blank initially. It displays the transformation type after you save the transformation.
Note: The Transformation panel doesn't display details of the physical model table. In case you click a physical model table after an entity and open the Transformation panel, it displays details of the last selected entity.
Select the attribute to which you want the transformed value applied, and click the Transformation icon .
The Transformation of column '<column name>' pop-up window is displayed.

Select the Function option, and from the Category drop-down list, select the category for the function you want to apply. You can perform one of these function transformations: choose from String, Numeric, Conversion, and Datetime.

String function transformation, 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 return an ASCII or Unicode integer representation of a character.

Conversion function transformation, such as convert or parse data types

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.

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.

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.

Click OK.
The applied function transformation is reflected on the canvas with a transformation node.
Click Save.

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:

Select the Product table on the canvas.
In the Transformations panel, click adjacent to the Name column, and in the Transformation of column 'NAME' pop-up window, select the Function option.
In the Category drop-down list, select String.
In the Name drop-down list, select the rtrim(string string) function.
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.
Click OK.

The applied function transformation is depicted on the canvas as below:

Example 2: Applying Numeric function transformation.

Select the productid column on Product table on the Create Mapping page.
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.
In the Category drop-down list, select Numeric.
In the Name drop-down list, select abs(integer number). Option to specify the required number is displayed below the Name field.
Click the number arrow, and use the column option to select CustID (Integer) column of customer_car table in the FramworkDB2 physical model.
Click OK.
This mapping is displayed on the canvas.

Applying a Function Transformation - discovery - 23.1

Spectrum Discovery Guide

Example 1 - Applying a String Function Transformation