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.

Example

The following vertical macro expression trims leading and trailing spaces from fields that the %Addresses% macro input field represents:

At run time, the task generates the following set of expressions to trim spaces from the fields that %Address% represents:

LTRIM(RTRIM(Street))
LTRIM(RTRIM(City))
LTRIM(RTRIM(State))
LTRIM(RTRIM(ZipCode))

Configuring a Vertical Macro

You can configure a vertical macro on the Expression tab of the Expression transformation or the Aggregate tab of the Aggregator transformation.

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:

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 data type, precision, and scale of the incoming fields. Use the data type of incoming fields when the incoming fields include more than one data type and when the expression does not change the data type 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 the data type as the incoming fields.

Note that you cannot define the precision and scale after you select the Input Field Type data type.

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.

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.

Example

A macro input field named %InputDates% represents the following source fields for a macro that converts the data to the Date data type:

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 to include the following fields:

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 application runs the task, it expands the expression vertically, as follows:

The application groups the data by store when it performs the aggregation and writes the results to the target.