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:
IIF(ISNULL (AccountID, 1,0)+IIF(ISNULL(AccountName, 1, 0)+IIF(ISNULL(ContactName, 1, 0)+IIF(ISNULL(Phone, 1, 0)+IIF(ISNULL(Email, 1, 0)...
Horizontal Expansion Functions
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_IFF% creates calculations similar to the following expression:
IIF(<field> >= <constantA>, <constant1>,
IIF(<field> >= <constantB>, <constant2>,
IIF(<field> >= <constantC>, <constant3>, 'out of range')))
- %OPR_SUM%
- 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.
1. Create 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.
3. In the transformation output field, configure the macro expression. Use a horizontal expansion function and include the macro input fields.
4. To 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.
5. To 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([Salary>=%IncomeMin%] AND [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), "Individual Contributor",
IIF (Salary>=50000 AND Salary<100000), "Manager",
IIF (Salary>=100000 AND Salary<150000), "Senior Manager", "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.
