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, as shown in the following image.
The synonym for DMTERM shows a RECTYPE of T, which would retrieve only End Date records, as shown in the following image.
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.
In the DMC:
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.
When you have completed the Join, it should appear, as shown in the following image:
The Column Selection window opens.
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 |