Macro Expression  

               

When we want to apply the Expression to multiple columns on a table or flat file, we can approach this by Expression Macro field in Expression and Aggregator transformation. We can do calculations and we can replace the null values to set of fields.

If you want to Apply the same Condition to Multiple fields then we can Use this Expression Macro.In Expression macro there are two Fields to perform any Expression on set of fields. 

       1) Input Macro filed.

       2) Output Macro filed.

We can Create Macro Fields in Expression transformation and in Aggregator Transformation.

Input Macro field:  Input macro is a field which can hold set of fields from source. It can hold those set of incoming fields in a defined field(variable). While Creating Input Macro field it allows us to select the input fields to hold in Input Macro field.

Output Macro field: Output macro field is used to show the output of Expression Macro. In this field we can define Our conditions and expression to defined Input macro field.
Navigation: Data Integration>>Expression transformation>>Properties tab>>Expression Tab.

                                  Data Integration>>Aggregator transformation>>Properties Tab>>Aggregate Tab.

Design of Macro Expression:

                   

Types of Macro Expressions:

                   1) Vertical      

                   2) Horizontal

                   3) Hybrid Macro (vertical + Horizontal)

1) Horizontal macro expression: It expands the expression in Horizontally and we can use this Horizontal macro expression in both expression and Aggregator. It has some Predefined functions.

2) Vertical macro expression: Vertical Macro expression expands the expression in Vertically. We can use this vertical macro expression in both expression and Aggregator.

3) Hybrid Macro expression: Hybrid macro expression in works as both Vertical and Horizontal expression. It is having predefined Horizontal functions in expression and Aggregator.

Horizontal Expression macro functions:

There are predefined Horizontal Expressions in Functions Tab.

1) %OPR_CONCAT%
               2) %OPR_CONCATDELIM%

               3) %OPR_IIF%

               4) %OPR_SUM%                      

1)  %OPR_CONCAT%: When you use this function, it will concatenate the set of Input fields which are in the Input Macro field.

2)  %OPR_CONCATDELIM%: When you use this function, it will concatenate the set of Input fields with Delimiter which are in the Input Macro Field.

3) %OPR_IIF%: When you use this function, when the condition is true it will return the Macro_Input_Field (second argument) else it will return 3rd. argument.

4) %OPR_SUM%: When you use %OPR_SUM% function it will return the sum of the fields from the input macro.

➔   Let’s see the Example Scenario of Hybrid Macro Expression which includes Vertical and     Horizontal macro Expressions.

vCalculating the Cumulative salary (Vertical Expansion) and Concatenating the data with delimiter (Horizontal Expansion).

  Step 1:

➢   Go to Data integration.

➢   Click on New select Mappings and click Create.

               

Step 2:

     Select Source, give the Source Connection and Source object Details in the source properties.

                

Step 3:

➢   Drag the Expression transformation to the design area.

➢   Click on the expression transformation go the Expression Tab, Create Two input Macro fields.

•      Input_cumulative               Input macro field

•      IN_concat                           Input macro field

•      Input_cumulative_out        Output macro field

•      Macro_Concat_output       Output filed

➢ Create Input macro field (Input_cumulative) by clicking Plus symbol.

                   

➢  After creating the Input macro field (Input_cumulative), Click on the expression select the Salary Column to calculate the cumulative.

➢   Create Output macro field (Input_cumulative_out) by clicking Plus Symbol. 

                              

                              

➢ After creating the Output macro expression filed (Input_cumulative_out) click on the Expression and write the expression CUME(SAL,% Input_cumulative%)

                 

➢   Create Input macro field by clicking the Plus symbol and Click on the expression, select the required columns to concatenate.

                 

➢  Create the Output filed to capture the output of horizontal macro expression and give the expression like %OPR_CONCATDELIM[%IN_concat%]%

                 

Step 4:

      Select the Target and provide Connections details and select the target type as single Object.

                  

➢   Click on the select option, create New at Run time and give the table name.

➢   Click on save and Run the mapping.

➢   Check the Data in the Target Table.

                   

Thank You

Nishith Charan Sannapulla 

MOURI Tech Pvt Ltd. ,

nishiths.in@mouritech.com