Combining Multiple Sources Rows in a Single Target Row

Sometimes data from multiple input rows needs to be combined into a single row. For example, you might encounter a situation where three separate records from a data source map to one record in the data target.

In the following example, the data source contains transaction records with an ID number, action code, and date. The action code, with values of either S or T, indicates whether the date is a Start Date or Termination Date. For example, the data source contains:

ID_NUM ACTION  ACT_DATE
------ ------  --------
    23 S       1997/03/26
    24 S       1995/08/24
    24 T       1999/02/16
    25 S       1995/10/05
    25 T       1999/01/15

The data flow takes the source data and flips, or pivots, it so that the data target contains one row for each ID_NUM, each with two date columns, one for the Start Date, and one for the Termination Date (if any).

This solution uses two different synonyms to describe the same source file. Each synonym recasts the ACTION column to a RECTYPE allowing the file to be split into Start Date records and End Date records A select against DMSTART automatically selects only those records with a RECTYPE of S and a select against DMTERM retrieves only those with a RECTYPE of T.

The synonym for DMSTART shows a RECTYPE of S which would retrieve only Start Date records.

Rectype example

The synonym for DMTERM shows a RECTYPE of T which would retrieve only End Date records.

Rectype Example

When the job is run, the output table for the ID_NUM values shown above is:

ID_NUM START_DATE  TERM_DATE
------  ----------  ---------
    23 1997/03/26  .
    24 1995/08/24  1999/02/16
    25 1995/10/05  1999/01/15

Since there was no T row in the input file for ID_NUM value 23, there is no TERM_DATE value in the output.

Note:

Refer to the sample data flow DMPIVOT for the complete example.


Top of page

Example: Combining Data Source Rows in a Single Data Target Row

In the DMC:

  1. Right-click an application directory in the navigation pane and choose New, then Flow. The Data Flow tab opens in the workspace, with the SQL object displayed.
  2. Drag the data source object DMSTART from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
  3. Drag a second data source object, DMTERM, into the workspace.
  4. To define the Join condition, right-click the Join object and select Join Editor.

    The Join Editor window opens.

    Note: Since the data sources are flat files without any key columns, no join conditions are defined automatically, even if that setting is in effect.

  5. Select ID_NUM in the Left and Right Source Columns lists and click the equal sign (=).
  6. To make sure that we obtain those records that have a START_DATE but not an END_DATE, the JOIN type should be a left outer Join. Click the left circle in the Join Type diagram.

    When you have completed the Join, it should appear, as shown in the following image:

    Join Editor

  7. Click OK to close the Join Properties window.
  8. Right-click the SQL object and select Column Selection.

    The Column Selection window opens.

  9. Select T1.ID_NUM, T1.START_DATE, and T2.TERM_DATE in the Available Columns list and click the arrow to add them into the Selected Columns list. Click OK.
  10. Right-click the workspace to the right of the SQL object and select Add Target, then New.
  11. In the Properties window, select the adapter you used for your sample procedures, the application directory you are using for your samples, and enter DMXEDATE as the synonym. Click OK.
  12. Save the data flow as DMXPIVOT.
  13. On the Flow tab, in the Run group, click Run and select Run from the drop-down menu.

    Note: Run ibisamp/dmrctyp first.

When the data flow run is complete, verify that the data was loaded properly by right-clicking the DMXEDATE object, selecting Toggle, and clicking the Sample Data tab.


iWay Software