Join

Header

In the header, you define the principal behavior of the join.

You can choose between different join types:

• Implicit Fields

  Fields coming from inputs are automatically considered during the join procedure. If the field is visible on multiple join rules (or inputs), you can use the Auto Filling function.

• Explicit Fields

  Fields of inputs are manually defined and maintained by the configurer. These fields will then be considered during processing.

The modeler can control the join results with the Auto Filling option. The following settings are possible:

• “No”: If a field is defined in a join rule in multiple inputs, the field content is taken from the first input that contains the field.

• “If Null then First to Last”: The first non-null value is taken and if all values are null, the initial value is returned for that field.
• “If Null/Initial then First to Last”: The first non-null and non-initial value is taken and if all values are null or initial, an initial value is returned for that field.

Input Tab

When the Input tab is assigned with an input function, a new dialog box that contains the complex selection and normal selection is available. In the complex selection, you can add a more sophisticated statement in order to retrieve the data.

Rules

Each join rule semantically defines the reading of a specific input.

Hierarchical join rules are also supported by assigning higher levels. The hierarchy of levels is resolved starting with the highest level, feeding as input to the lower levels and ending with level 0.

The following rule types are available:

1. From: This is always the first rule of a level.
2. Left Outer Join: This join type returns all rows from the rule above, and the columns and rows from this rule, where the predicates match.
3. Inner Join: This join type returns all rows when there is at least one predicate match in the rule above and this rule.
4. Full Outer Join: This join type returns all (matched or unmatched) rows from both the rule above and this rule.
5. Cross Join: This join type returns the cartesian product of the rule above and this rule.
6. Union All: This join behaves in the same way as a union, but duplicate records are not removed.
7. Lookup: Looks up fields and fills them in the first non-lookup rule above where the predicates match. At least one field needs to be defined as a lookup field.
8. Lookup Auto Predicate: Looks up fields and fills them in the first non-lookup rule where all common fields match those of the input function which is set on the Rule tab (this means, Field 1 of Rule 1 = Field 1 of Rule 2). At least one field needs to be defined as a lookup field.

Sub View

You can define further selections, formulas, aggregations and sorting orders for each rule.

Complex Selections

If required, you can define complex selections using formulas and SQL functions.

Join Predicates

You can define the predicate conditions for the matching for join and lookup rules here.

Complex Predicates

If required, you can enter complex on-predicates for join and lookup rules here using formulas and SQL functions.

Function Access

Follow the steps below to access the Join function:

1. In the client where SAP Profitability and Performance Management is installed, choose SAP Menu  Profitability and Performance Management  Modeling  Start My Environments.

2. The Environment screen appears in a separate browser window. Choose an existing environment and continue. Within the environment, you can set up the newly added function.

Function Configuration

Follow the steps below to configure the Join function:

1. In edit mode, configure the following required fields in the header. For more information about the header, see the Key Features section above.

   • Join Type
   • Result Handling
   • Suppress initial Result
   • Include original Input Data
   • Result Model Table

2. Define the input function to be used on the Input tab.

3. Define the fields to be used on the Signature tab.

4. On the Rules tab, you can define the rule types of the Join function.

   Proceed as follows to configure the rules:

   1. On the Rule tab of your Join function, choose  (Add).

   2. The Add Details screen is displayed. Enter the following information:

      • Add Level
      • Rule
      • Rule Type
      • Input Description

   3. Choose OK.

   4. Select the created rule.

   5. Define the input function to be used.

   6. On the Subview tab created rule, choose  (Add).

   7. The Fields screen appears where you can select the fields to be used.

   8. Choose OK.

   9. Follow the steps below if you want to setup or apply a formula for a specific field (optional):

      1. Select the field and choose f (Formula).

      2. On the Formula screen which is displayed next, enter the formula.

      3. Choose OK.

   10. Follow the steps below if you want to setup or apply a selection condition for a specific field (optional):

       1. Select the field and choose Selection Condition.

       2. On the Select Condition screen which is displayed next, enter the condition.

       3. Choose OK.

   11. On the Complex Selections tab of the created rule, you can directly define complex selections using formulas and SQL functions.

   12. On the Join Predicates tab of the created rule, choose  (Add).

   13. The system automatically adds a new line entry. Enter the following information here:

       • Field
       • Comparison
       • Join Rule
       • Join Field

   14. On the Complex Predicates tab of the created rule, you can directly enter “on” predicates for join and lookup rules using formulas and SQL functions.

5. Define the checks to be used on the Checks tab, if needed.

6. Choose Save and then choose Activate.