Joining Data

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Designing Mappings >

Joining Data

Sometimes, you may need to combine data from two or more structures based on some condition (for example, if field A in the first structure has the same value as field B in the second structure). For such mapping requirements, a Join component can be used.

 

A Join component is a MapForce component which enables joining two or more structures on the mapping based on custom-defined conditions. It returns the association (joined set) of items that satisfy the condition. Joins are particularly useful to combine data from two structures which share a common field (such as an identity).

 

For example, on the mapping illustrated below, the middle component is a "Join" component. In this mapping, two XML structures (a list of people and a list of addresses) are being joined. The goal here is to get the full details of each person into a target XML file. The FirstName and LastName fields act as joining keys. Namely, if value of FirstName and LastName (under Person) is the same as that of FirstName and LastName (under Address), the address details belong to one and the same person and they become "joined". Any items from the joined structure can further be mapped to a subsequent target (in this case, an XML file). The join condition itself is defined in the properties of the Join component, by clicking the Define Join Condition ( mf_ic_join_key ) button. This example is accompanied by a mapping sample and is explained in more detail in Example: Join XML Structures.

mf_map_JoinPeopleInfo

JoinPeopleInfo.mfd

As illustrated above, the source structures and the Join component are connected by means of "Copy-All" connection, which reduces the mapping clutter. In general, such connections are created automatically by MapForce when the context is relevant (for more information, see Copy-All Connections).

 

The structures that are to be joined may either be from separate components (as in the mapping above), or belong to the same component. The structures to be joined may also be of different kinds (for example, an XML structure and a database table). For more information about database-related joins, see Joining Database Data.

 

 

To add a Join component:

1.Set the mapping transformation language to BUILT-IN (to do this, either click the ic-builtin toolbar button, or use the Output | Built-In Execution Engine menu command).
2.On the Insert menu, click Join. Alternatively, click the Join ( mf_ic_join_button ) toolbar button. The Join component appears on the mapping. By default, it accepts data from two structures, so it has two nodes/rows inputs. If necessary, you can add new inputs to the join by clicking the Add Input ( gui_add_function_parameter ) button, see Joining Three or More Structures.

mf_join

3.Connect the structures that are to be joined to the nodes/rows items of the join component.
4.Add the condition for the join (or multiple conditions). To do this, right-click the Join component and select Properties. Join conditions can also be added directly from the mapping, by connecting the Boolean result of some function to the condition item of the Join component. In certain cases when database tables are joined, the join condition (or conditions) can be created automatically by MapForce. For further information, see Adding Join Conditions.

 

Notes:

 

Join components are supported when the target language of the mapping is set to BUILT-IN. Code generation in C#, C++, or Java is not supported.
When a structure is not a valid or supported input source for the join, MapForce displays hints either immediately directly on the mapping, or in the Messages window, when you validate the mapping (see Validating Mappings).
Join components should not be connected to input parameters or results of inline user-defined functions. If such connections exist, validation errors will occur during mapping validation.
When you connect eligible database components (such as tables or views) directly to a Join component, an SQL mode ( mf_ic_join_sql_enabled ) button automatically appears at the top-right corner of the Join component. When enabled, this button provides special SQL features applicable to the join operation (see About Joins in SQL Mode).
It is not possible to connect the output of the joined item to another Join component. If necessary, however, you can connect a partial result of one join to another one.

 

Join components compared to other component types

In some cases, complex variables or filters can be used instead of Join components to achieve the same results (see Using Variables and Filters and Conditions, respectively). However, unlike other component types, Join components make the mapping easier to understand, because you can see at a glance the data that is being joined. Additionally, if SQL mode is enabled on the join component, the mapping performance improves significantly (this applies to database joins, see Joining Database Tables).

 

Adding a parent context

In some special cases, in order to achieve a specific mapping result, you can explicitly provide a mapping context (a so-called "parent context") for data connected to the Join component. To add a parent context, right-click the joined item of the Join component, and select Add Parent Context from the context menu. The Join component changes appearance to include an additional parent-context input where you can connect the required source item. For more information, see Overriding the Mapping Context.


© 2019 Altova GmbH