Open topic with navigation

Skip If Repeated Example

Source Table/Query Fields

Order Number, Order Date, Item_Number, Part_Number, Quantity, and Extended_Price

The Scenario

The Source contains an order with three items followed by an order with two items.

Three rows contain varying item data (Item_Number, Part_Number, Quantity, and Extended_Price) and redundant order header data (Order Number).

On the fourth row, the order header data changes and is repeated on the fifth row. Item data continues to vary.

Note: Order Number values in bold are used here to emphasize the redundant data.

The Target Steps

• Step 1 — Insert into the Order Header table.
• Step 2 — Insert into the Order Item table.

Define the Skip field

Designate Order Number as a Skip field in Step 1. This causes redundant Order Header data in subsequent rows to be skipped.

Note: You should ensure that your source data is sorted by the field(s) that will be selected as Skip fields, otherwise you will not get the desired results.

Processing

When the first three Source rows are processed, only one Order Header record will be inserted but all three Order Item records will be inserted. (The Order Number field — the Skip field — is the same for each of the three rows.)

On the fourth row the changed Order Header record will be inserted, followed by two Order Item records for the fourth and fifth rows.

In contrast, not defining a Skip field in this example would result in the Order Header record being inserted for all five source rows. This would slow processing considerably, and may result in either duplicate records in the Target database or failed operations.

TIBCO Scribe® Insight Help Library