An expression macro is a macro that you use to create repetitive or complex expressions in mappings.
You can use an expression macro to perform calculations across a set of fields or constants. For example, you might use an expression macro to replace null values in a set of fields or to label items based on a set of sales ranges.
In an expression macro, one or more input fields represent source data for the macro. An expression represents the calculations that you want to perform. And an output field represents the results of the calculations.
At run time, the task expands the expression to include all of the input fields and constants, and then writes the results to the output fields.
You can create expression macros in Expression and Aggregator transformations but you cannot combine an expression macro and an in-out parameter in an Expression transformation.
Macro types
You can create the following types of macros:
Vertical
A vertical macro expands an expression vertically. The vertical macro generates a set of similar expressions to perform the same calculation on multiple incoming fields.
Horizontal
A horizontal macro expands an expression horizontally. The horizontal macro generates one extended expression that includes a set of fields or constants.
Hybrid
A hybrid macro expands an expression both vertically and horizontally. A hybrid macro generates a set of vertical expressions that also expand horizontally.
Macro input fields
A macro input field is a field that represents input that you want to use in the expression macro. The input can be fields or constants. All expression macros require a macro input field.
A macro input field in a vertical macro represents a set of incoming fields.
A macro input field in a horizontal macro can represent a set of incoming fields or a set of constants. You can create a multiple macro input fields in a horizontal macro to define multiple sets of constants.
Macro input fields use the following naming convention: %<macro_field_name>%.
For example, you want to apply an expression to a set of address fields. You create a macro input field named %AddressFields% and define a field rule to indicate the incoming fields to use. When you configure the expression, you use %AddressFields% to represent the incoming fields.
Vertical macros
Use a vertical macro to apply a macro expression to a set of incoming fields.
The macro input field in a vertical macro represents the incoming fields. The expression represents the calculations that you want to perform on all incoming fields. And the macro output field represents a set of output fields that passes the results of the calculations to the rest of the mapping. You configure the macro expression in the macro output field.
The macro output field represents the output fields of the macro, but the names of the output fields are not explicitly defined in the mapping. To include the results of a vertical macro in the mapping, configure a field rule in the downstream transformation to include the output fields that the macro generates.
To write the results of a vertical macro to the target, link the output fields to target fields in the Target transformation.
When the task runs, the task generates multiple expressions to perform calculations on each field that the macro input field represents. The task also replaces the macro output field with actual output fields, and then uses the output fields to pass the results of the calculations to the rest of the mapping.
Note: The macro output field does not pass any data.
Example
The following vertical macro expression trims leading and trailing spaces from fields that the %Addresses% macro input field represents:
LTRIM(RTRIM(%Addresses%))
At run time, the task generates the following set of expressions to trim spaces from the fields that %Address% represents:
You can configure a vertical macro on the Expression tab of the Expression transformation or the Aggregate tab of the Aggregator transformation.
1Create a macro input field to define the incoming fields to use.
2Create a macro output field to define the datatype and naming convention for the output fields.
3In the macro output field, configure the macro expression. Include the macro input field in the macro expression.
4In the downstream transformation, configure a field rule to include the results of the macro in the mapping.
Macro input fields for vertical macros
Use a macro input field to represent the incoming fields that you want to use in a vertical macro.
When you create a macro input field, define a name for the macro input field, and then use field rules to define the incoming fields that you want to use. At run time, the macro input field expands to represent the selected fields.
You can use the following field rules when you configure a macro input field:
•All Fields
•Named Fields
•Fields by Text or Pattern
The following image shows a Named Fields field rule that includes the Q1 to Q4 fields:
Macro output fields for vertical macros
A macro output field represents the output fields that the task generates at run time for a vertical macro. You also define the expression that you want to use in the macro output field.
When you configure a macro output field, you select the macro input field to use and define a naming convention for the output fields. You can customize a prefix or suffix for the naming convention. By default, the macro output field uses the following naming convention for output fields: <macro_input_field>_out.
You can define the data type, precision, and scale of the output fields. Or, you can configure the macro output field to use the datatype, precision, and scale of the incoming fields. Use the datatype of incoming fields when the incoming fields include more than one datatype and when the expression does not change the datatype of incoming data.
At run time, the task generates output fields based on the macro output field configuration. The task creates an output field for each incoming field that the macro input field represents, and then writes the results of the expression to the output fields.
For example, the following image shows a macro output field that creates output fields based on the incoming fields that %QuarterlyData% represents:
If the %QuarterlyData% macro input field represents the Q1 to Q4 fields, then the task creates the following output fields at run time: Q1_out, Q2_out, Q3_out, Q4_out. The output fields have the same datatype as the incoming fields.
Note that you cannot define the precision and scale after you select the Input Field Type datatype.
Field rules for vertical macro output fields
To use the results of a vertical macro in a mapping, configure a field rule to include the output fields in the downstream transformation or parameterize the target field mapping.
Because an expression macro represents fields that are not explicitly defined until run time, you need to configure the downstream transformation to include the output fields of a vertical macro. There are two ways to do this:
•Create named fields in the downstream transformation. On the Incoming Fields tab, create a named field rule and create a new incoming field for each output field of the vertical macro. You can use these fields in downstream transformations.
•Alternatively, if your Target transformation is directly downstream from the macro, completely parameterize the target field mapping. When you configure the mapping task, Data Integration creates the macro output fields in the target. Map the incoming fields to the target fields.
Example
A macro input field named %InputDates% represents the following source fields for a macro that converts the data to the Date data type:
OrderDate ShipDate PaymentReceived
The macro output field uses the default naming convention: <macro input field>_out. To use the Date fields that the macro generates, create a Named Field rule in the downstream transformation. Create the following fields:
OrderDate_out ShipDate_out PaymentReceived_out
Configure the field rule to include the fields that you create.
After you create the field rule, you can use the fields in expressions and field mappings in downstream transformations.
Vertical macro example
To find the annual sum of quarterly data for each store, you might use a vertical expression macro in an Aggregator transformation.
The Aggregator transformation uses the store ID field as the group by field. A %QuarterlyData% macro input field reads sales data from the following input fields: Q1, Q2, Q3, and Q4.
A %QuarterlyData%_out macro output field is based on the %QuarterlyData% macro input field. To find the sum of sales for each quarter, the macro output field includes the following expression: SUM(%QuarterlyData%).
In the Target transformation, a field rule includes the following output fields in the incoming fields list: Q1_out, Q2_out, Q3_out, Q4_out. In the target field mapping, the Qx_out fields are mapped to the target.
The following image shows the vertical expression macro in an Aggregator transformation:
When the task runs, the expression expands vertically, as follows:
SUM(Q1) SUM(Q2) SUM(Q3) SUM(Q4)
The task groups the data by store when it performs the aggregation and writes the results to the target.
Horizontal macros
Use a horizontal macro to generate a single complex expression that includes a set of incoming fields or a set of constants.
In a horizontal macro, a macro input field can represent a set of incoming fields or a set of constants.
In a horizontal macro, the expression represents calculations that you want to perform with the incoming fields or constants. The expression must include a horizontal expansion function.
A horizontal macro expression produces one result, so a transformation output field passes the results to the rest of the mapping. You configure the horizontal macro expression in the transformation output field.
The results of the expression pass to the downstream transformation with the default field rule. You do not need additional field rules to include the results of a horizontal macro in the mapping.
To write the results of a horizontal macro to the target, connect the transformation output field to a target field in the Target transformation.
Example
For example, a horizontal macro can check for null values in the fields represented by the %AllFields% macro input field. When a field is null, it returns 1. And then, the %OPR_SUM% horizontal expansion function returns the total number of null fields.
The following expression represents the calculations in the macro:
%OPR_SUM[ IIF(ISNULL(%AllFields%), 1, 0) ]%
At run time, the application expands the expression horizontally as follows to include the fields that %AllFields% represents:
Use a horizontal expansion function to create an expression in an expression macro.
Horizontal expansion functions use the following naming convention: %OPR_<function_type>%. Horizontal expansion functions use square brackets ([ ]) instead of parentheses.
In the Field Expression dialog box, the functions appear in the Horizontal Expansion group of the functions list.
You can use the following horizontal expansion functions:
%OPR_CONCAT%
Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields. %OPR_CONCAT% creates calculations similar to the following expression:
FieldA || FieldB || FieldC...
%OPR_CONCATDELIM%
Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields, and adds a comma delimiter. %OPR_CONCATDELIM% creates calculations similar to the following expression:
FieldA || ", " || FieldB || ", " || FieldC...
%OPR_IIF%
Uses the IIF function and expands an expression in an expression macro to evaluate a set of IIF statements. %OPR_IIF% creates calculations similar to the following expression:
Uses the SUM function and expands an expression in an expression macro to return the sum of all fields. %OPR_SUM% creates calculations similar to the following expression:
FieldA + FieldB + FieldC...
For more information about horizontal expansion functions, see Function Reference.
Configuring a horizontal macro
You can configure a horizontal macro on the Expression tab of the Expression transformation or the Aggregate tab of the Aggregator transformation.
Configure a horizontal macro based on whether you want to use incoming fields or constants in the macro expression.
1Create one or more macro input fields:
- To use incoming fields, create a macro input field to define the incoming fields to use.
- To use constants, create a macro input field for each set of constants that you want to use.
2 Create a transformation output field.
3In the transformation output field, configure the macro expression. Use a horizontal expansion function and include the macro input fields.
4To include the results of a horizontal macro in the mapping, use the default field rule in the downstream transformation. You can use any field rule that includes the transformation output field.
5To write the results of a horizontal macro to the target, connect the transformation output field to a target field in the Target transformation.
Macro input fields for incoming fields in horizontal macros
You can use a macro input field to represent the incoming fields that you want to use in a horizontal macro.
When you create a macro input field, define a name for the macro input field, and then use field rules to define the incoming fields that you want to use. At run time, the macro input field expands to represent the selected fields.
You can use the following field rules when you configure a macro input field:
•All Fields
•Named Fields
•Fields by Text or Pattern
The following image shows a Named Fields field rule that includes the Q1 to Q4 fields:
Macro input fields for constants in horizontal macros
You can use a macro input field to represent the constants that you want to use in a horizontal macro. You can also create multiple macro input fields to represent corresponding sets of constants.
When you create a macro input field, define a name for the macro input field, and then define the constants that you want to use. At run time, the macro input field expands to represent the constants and uses them in the listed order.
When you create multiple macro input fields with corresponding sets of constants, the task evaluates each set of constants in the listed order.
The following image shows a macro input field that represents constants:
At run time, the macro input field expands and uses the constants in the following order: 50000, 100000, 150000.
Transformation output field configuration for horizontal macros
Use a transformation output field to define the expression for a horizontal macro and to pass the results to the rest of the mapping.
When you create a transformation output field, you define the name and datatype for the field. You also configure the expression for the macro. In the expression, include a horizontal expansion function and any macro input fields that you want to use.
The default field rule passes the transformation output field to the downstream transformation. You can use any field rule that includes the transformation output field to pass the results of a horizontal macro to the mapping.
Horizontal macro example
To create categories for employees based on salary ranges, you might create a horizontal macro that defines the minimum and maximum values for each range and corresponding job categories.
In an Expression transformation, macro input fields define the constants to use in the expression. %IncomeMin% defines the low end of each salary range and %IncomeMax% defines the high end of each salary range. %EmployeeType% lists the job category that corresponds to each range.
The EmployeeStatus transformation output field passes the results to the mapping and includes the following horizontal macro expression:
%OPR_IIF[ (EMP_SALARY>=%IncomeMin%) AND (EMP_SALARY<%IncomeMax%), %EmployeeType%, 'unknown' ]%
In the Target transformation, the default field rule includes the EmployeeStatus transformation output field in the incoming fields list. In the target field mapping, the EmployeeStatus is mapped to the target.
The following image shows the horizontal macro in an Expression transformation:
The horizontal macro expression expands as follows when you run the task:
IIF(Salary>=5000 AND Salary<50000), 'IndividualContributor', IIF (Salary>=50000 AND Salary<100000), 'Manager', IIF (Salary>=100000 AND Salary<150000), 'SeniorManager', 'unknown')))
Note that the expression uses the first value of each macro input field in the first IIF expression and continues with each subsequent set of constants.
Hybrid macros
A hybrid macro expands an expression both vertically and horizontally. A hybrid macro generates a set of vertical expressions that also expand horizontally.
Configure a hybrid macro based on your business requirements. Use the configuration guidelines for vertical and horizontal macros to create a hybrid macro.
Example
For example, the following expression uses the %OPR_IIF% horizontal expansion function to convert the format of the date fields represented by the %dateports% macro input field to the 'mm-dd-yyyy' format:
The expression expands vertically to create an expression for the StartDate and EndDate fields that %dateports% represents. The expression also expands horizontally to use the constants that %fromdateformat% represents to evaluate the incoming fields.
