Use Advanced Mapping

Enable Advanced Mapping

To enable advanced mapping in your interface: 

Do one of the following: 

• On the Worksheet tab, in the Mapping group, click Enable Advanced Mapping.

  Open

  

• At the bottom of the toolbox, click the Advanced Mapping button so it displays a value of ON.
  
  

  Open

  

When you enable advanced mapping, the system displays the following Advanced Mapping shapes in the Shapes area of the toolbox:

• Variable

• Raise Exception

• Lookup

• Date Rule

• Native Code

The system also divides the worksheet area into vertical bands.

Disable Advanced Mapping

You can switch back to normal mapping later. You first need to remove all the advanced mapping controls from the worksheet.

To disable advanced mapping in your interface: 

Do one of the following: 

• On the Worksheet tab, in the Mapping group, click Disable Advanced Mapping.

• At the bottom of the toolbox, click the Advanced Mapping button so it displays a value of OFF.

Manage the Bands

After you enable advanced mapping, the system divides the worksheet area into vertical bands. Two bands, Mashups 1 and EachRow, become visible right away because they are most likely to be used.

Show or Hide Bands

To show or hide available bands:

You can click  to the left of the band captions and select the check box for each band you want the system to show. The system hides the bands that have a cleared check box.

A description of each band follows. 

Mashups 1, Mashups 2, Mashups 3

Three mashup bands are reserved for the mashups–data objects which join to the main data set. Mashup icons can only be used in the first three bands. Applying the mashups is always the first action at run time. This is why only mashup bands can contain mashups.

It does not matter into which of the three mashup bands you place a particular mashup. All the bands are equal, and behind the scenes they are merged together into one. Spreading out multiple mashups across the three bands is simply more convenient than having one long vertical list of mashups in one band.

OnStart

The system executes instructions in this band before the processing starts, right after the mashups are joined with the main dataset. This is the place where you may want to set initial values to the variables and perform actions, which only need to be done once.

OnGroupStart

The system executes the instructions from this band before each new group starts. You specify the grouping key in the Group Key box expression. If you do not specify a Group Key value, the system treats all the records as if they belonged to one big group.

The Group Key box appears in the Solution tab on the Properties pane. If the Properties floating pane does not appear, at the right side of the window click Properties to expand the Properties pane. 

OnChildStart

This band contains instructions for the case when one of the incoming rows (usually in XML or JSON format) has a complex child node. The definition of the incoming dataset should have appropriate definitions of such, using SINK_set and SINK_record syntax.

EachRow

This band contains instructions for each row of the mashed-up source dataset.

OnChildEnd

(Reserved for future use.)

OnGroupEnd

This is the place to do any actions associated with group end. Output group-level aggregate functions, for example.

OnAfterGroupEnd

Similar to the onGroupEnd, but is mostly used for the roll-up functionality after the group processing ends.

OnEnd

Finalization of the processing. Generation of final footer with potentially global group-independent aggregations.

Use Mashups

A mashup element is a data object. It joins to other data objects by the key. Most often it joins to the main source data object, but in some scenarios cascading joins may be used.

Data for the mashups are stored in the Redis cache, and therefore:

• Mashups do not execute requests to the database at runtime

• The cache should be prepopulated

• Filters do not affect the mashups.

Use Variables

You can place a variable into any band except a mashup band.

Assign a Value to a Variable

After you drop the Variable shape onto the worksheet, you can quickly assign a value to it by dragging an element from the source grid or from another shape into the expression box.

To specify a more complex expression consisting of multiple source elements and functions, click the Expression label and use the expression bar at the top of the worksheet.

The system automatically assigns the variable's name to it at the drop action.

Edit the Variable Name

If you need to change the variable name, click  Edit beside the variable name in the Properties section of the Properties floating pane.

If other elements in the worksheet depend on this variable, the system displays a confirmation dialog box with the full list of places where it will replace the name.

Change the Variable Scope

You also can specify the variable scope. By default, all variables are local. Changing the scope to global makes the variable "remember" its value between the records. This is a useful feature for counters and aggregation variables. The system renders global variables with a yellow header.

If you need to change the scope, select the variable and change the Scope box value from LOCAL to GLOBAL in the Properties section of the Properties floating pane.

Redefine a Variable

It is possible to redefine the variables, that is, to reuse a variable name in multiple shapes. This ability is available starting with EDS version 2.0.10.64 (IWS_2021_07_09 release),

Raise Exceptions

The Raise Exception shape allows you to generate an exception based on a condition, designate error text, and choose what happens when the exception occurs.

You can place the Raise Exception shape into any band except a mashup band.

After you drop the Raise Exception shape onto the worksheet, you can define how it works by dragging an element from the source grid or from another shape into the expression box. You can see a connecting arrow from the starting location to the exception box connector. Or you can drag the source grid line number to the connector, and the expression changes to that variable value. 

To enter the value for any of the elements in the exception box:

1. Click the field.
   For example, Condition. 

2. Select the expression bar at the top of the page. 

3. Type the expression. 

4. Click the green check box beside the field in the exception box to apply your changes. 

The Raise Exception shape has the following elements:

• Condition. An expression with Boolean result. If evaluated to 1 (True), it triggers the exception.

• Severity. From the Severity list, select what happens when the exception is raised. You can select Skip Failed to skip only the current failed record, and continue the processing. Or you can select Stop Processing to stop the processing altogether.

• Error Message. Text message (expression) which is shown in the logs and processing status when the exception is raised.

• Error Code. Numeric code assigned to the exception.

Use Date Rules

The EDS Date Rule element allows you to use a date rule defined in the environment.

You can place the Raise Exception shape into any band except a mashup band.

After you drop the Date Rule shape onto the worksheet, you can define how it works by dragging an element from the source grid or from another shape into the expression box. You can see a connecting arrow from the starting location to the daterule box connector. Or you can type in an expression manually use the expression bar at the top of the page. 

The Date Rule element has the following properties:

• Condition. Condition to execute, For example, |SOURCE_NAME| = 'EAGLE PACE'

• Rule Name. Expression with date rule name/identifier. For example, 'Prior Business Day'

• Date Value. Expression for base date for which date rule is calculated, For example, today()

• Provider. Name of the daterule evaluation provider/vendor. As of now only pace engine is available. You can leave the parameter blank.

• Result. The result of the date rule execution can be read from the variable :shapeName_RESULT:

• ErrMessage. Any execution errors are saved into variable :shapeName_ERROR_MSG:"

Use the Lookup

The Lookup shape allows you to execute a stored procedure and get back the result.

You can place the Lookup shape into any band except a mashup band.

To use the Lookup shape:

1. After you drag the Lookup shape into the worksheet, click the Set Source link in the bottom of the Lookup box.

   You see the Select data box with the Select SP tab selected by default.

2. Expand a database and select the stored procedure. 

3. Click Next.
   You see the Parameters Preview tab. 

4. In the Input parameters grid, review the incoming parameters. If you need to populate the input with specific values, click the Get Cursor button.
   If the procedure has required incoming fields or the output fields that depend on what you pass in, you may need to populate the input with specific values. In the Input parameters grid, the input values appear in the Test Value column.

5. In the Output parameters grid, populate the output cursor. You can click the Get Cursor button to fill the grid.
   The Get Cursor button actually runs the stored procedure with the test parameters which you provided in the Input parameters grid, and builds the list of fields which come back from the procedure. This cursor is later used in the Lookup element as the list of available output fields.

6. Click OK.

Use Native Code

The Native Code shape allows you to use json-formatted definitions of the elements supported by the backend, but not yet implemented in the graphical user interface (GUI) as separate control.

You can place the Lookup shape into any band except a mashup band.

For example, you can paste RaiseException into the Native Code element as the following json.

To convert any element into the raw form, on the Worksheet tab, in the Shape group, click ToCodeSnippet.

To convert the content of Native Code element into supported graphical elements, on the Worksheet tab, in the Shape group, click To Shapes.

It is possible to convert multiple elements at once. Unsupported elements are left in the "Native Code" containers. See the following figure.