Expression transformation is a connected, passive transformation used to calculate values on a single row.
Expression transformations are used for row-wise manipulation.
For any manipulation, you wish to perform on an individual record, use an Expression transformation.
The Expression transformation accepts the row-wise data, manipulates it, and passes it to the target.
The transformation receives the data from the input port and sends the data out from output ports.
An expression transformation is created with following types of ports.
- Input port (I)
- Output Port (O)
- Variable Port (V)
- Data Manipulation : concatenation( CONCAT or || ) , Case change (UPPER,LOWER) truncation, InitCap (INITCAP)
- Datatype conversion : (TO_DECIMAL, TO_CHAR, TO_DATE)
- Data cleansing – check nulls (ISNULL) , replace chars, test for spaces (REPLACESTR) , test for number
- Manipulate dates – convert, add, test (GET_DATE_PART, IS_DATE, DIFF_DATES)
- Scientific calculations and numerical operations – exponential, power, log, modulus (LOG, POWER, SQRT)
- ETL specific – if, lookup, decode (IIF, DECODE).
Open folder where we want to create the mapping.
Click Tools -> Mapping Designer.
Click Mapping -> Create -> Give mapping name. Ex: m_totalsal
Drag EMP from source in mapping.
Click Transformation -> Create -> Select Expression from list. Give name and click Create. Now click done.
Now create a new port out_Total_SAL. Make it as output port only.
Click the small button that appears in the Expression section of the dialog box and enter the expressi
Enter expression SAL + 10000 . You can select SAL and 10000 from Ports tab in expression editor.
Check the expression syntax by clicking Validate.
Click OK -> Click Apply -> Click Ok.
Now connect the ports from Expression to target table.
Click Mapping -> Validate
Repository -> Save
