Working With Joins

How to:

A join is a temporary connection between two or more data sources that share at least one common field. After you join two data sources, each time WebFOCUS retrieves a record from the first data source (host file), it also retrieves the matching records from the second data source (target file).

Advanced Graph Assistant provides a graphical method for creating and manipulating joins. You can also create define-based joins and multi-field joins.
Top of page
x
Procedure: How to Create a Join
  1. Click the Data selection tab.
  2. Click the Join tab, located to the right of the Available Fields tab.
  3. Click the Join Join button button.

    The Please pick a target master file dialog box opens.

  4. Select a Master File and click OK.

    The Create Join dialog box opens, as shown in the following image.

    Create Join dialog box

  5. In the Description box, give the join a meaningful and unique name.
  6. Select one of the following join Type options:
    • Default. For an existing join. Utilizes the existing join type.
    • Inner. An Inner join omits the source file rows that lack corresponding rows in the target file.
    • Left Outer. A Left Outer join extends the results of an Inner Join and retrieves records from both the source and target files. This type of join includes all records from the source file and any records from the target file where the condition values match. If there are no matching values in the target file, the join still retrieves records from the source file.
  7. Select one of the following join Instances options:
    • Multiple. A Multiple Instances join is a one-to-many join structure that matches one value in the source file to multiple values in the target file.

      For example, joining the employee ID field in an employee data source to the employee ID field in a company training data source that lists all training classes offered to employees would result in a listing of all courses taken by each employee, or a joining of the one instance of each ID in the source file to the multiple instances of that ID in the target file.

    • Single. A Single Instance join is a one-to-one join structure that matches one value in the source file to one value in the target file.

      For example, joining the employee ID field in an employee data source to an employee ID field in a salary data source would result in a single instance of salary data for each employee ID.

  8. Select an appropriate indexed field from the source file using the Select a source field drop-down list below the Source Fields heading. You can also select multiple indexed fields from the source file when creating a multi-fields join.

    Note: Fields from the source and target files must have the same format and be indexed fields in order to use them to create joins. Indexed fields are identified by the key Indexed fields icon icon.

  9. Select an appropriate indexed field from the target file using the Select a target field drop-down list below the Target Fields heading.

    Note: If you selected a field from the source file that does not have a matching indexed field with the same format in the target file, the message 'There were no fields found" is displayed instead of a Target Fields drop-down list.

  10. Click the Save & Create button to create the join.

    The new join appears in the Joins list.
Top of page
x
Procedure: How to Create a Define-based Join
  1. Click the Data selection tab.
  2. Click the Available Fields tab, located below the Data selection pane.
  3. Click the down arrow to the right of the define Define field icon with drop-down arrow button.
  4. Click the New define field option.

    The Field Creator dialog box opens, as shown in the following image.

    Field Creator dialog box

  5. Create a defined field by typing a name in the Field box, creating the desired function or expression in the input area, selecting the desired Format, and clicking OK.

    The new define field, prefixed with the function symbol predefined function define field icon icon, appears in the Available Fields list.

    If you want to delete or edit an existing define, select the define field, click the down arrow next to the define icon, and select one of the options, as shown in the following image.

    delete a define field with the Define icon drop-down arrow

  6. Add the defined field when creating a join. For details, see How to Create a Join.

    Note: Fields must have the same format and be indexed fields in order to use them to create joins.
Top of page
x
Procedure: How to Delete a Join
  1. Data selection tab.
  2. Click the Joins tab below the Data selection pane.
  3. Select the join you want to delete.
  4. Click the X (delete) button above.
Top of page
x
Procedure: How to Edit a Join
  1. Click the Data selection tab.
  2. Click the Joins tab below the Data selection pane.
  3. Double-click the join you want to edit.

    The Create Join dialog box opens.

  4. Make the desired edits to the join.
  5. Click the Save & Create button to save the edited join.
WebFOCUS