14. Get the Order Line with preferred Products (basic deduplication with defined values)

Note: The scenario below uses data from the DESelect Demo Data Package.

Scenario: A customer can have multiple Order Lines. You want to get one Order Line per customer, and you want to pick the Order Lines for products in the following order:

- product Running Jacket if there is an Order Line for this product
- product VLTG Bomber Jacket if there is an Order Line for this product and no Order Line for Running Jacket
- product Bose Bluetooth Earphones if there is an Order Line for this product an no Order Line for VLTG Bomber Jacket.

Note: this scenario uses feature Basic Deduplication, which is available in DESelect Plus.

1. Input

On the selections overview screen, click on New to create a new selection. 

Drag data extension DESELECT_DEMO_Orders to the Selected Data Extensions section.

Drag data extension DESELECT_DEMO_Order_Lines to the Selected Data Extensions section and drop it on top of DESELECT_DEMO_Orders.

Create the relation:

• Orders field: Id
• DESELECT_DEMO_Orders WITH MATCHING DESELECT_DEMO_Order_Lines
• Order Lines field: OrderId

Drag data extension DESELECT_DEMO_Products to the Selected Data Extensions section and drop it on top of DESELECT_DEMO_Order_Lines.

Create the relation:

• Order Lines field: ProductId
• DESELECT_DEMO_OrderLines WITH MATCHING DESELECT_DEMO_Products
• Products field: Id

If we would run the selection like this, we would get all Order Lines. However, we're only interested in one Filter Line per Product: the one with the highest Quantity. We'll add prio deduplication rules in step 2.

2. Output

Hit the Create Data Extension button, enter a name, eg. Orders with preferred products, and press Save.

Select the following fields by either double-clicking on them or dragging them to the section on the right:

• ContactId from DESELECT_DEMO_Orders
• OrderId from DESELECT_DEMO_OrderLines
• Name from DESELECT_DEMO_Products

Click Save Data Extension.

Now, let's add the prio deduplication rules.

Click on the Settings icon on the upper right corner of the Target Data Extension screen, and choose Prio Deduplication.

• Enable the Use Deduplication switch.
• Set ContactId as the unique field (this is the product name).
• Press Next.
• Choose Prio Deduplication Mode Basic.
• As the Field that determines the priority, choose Name.
• Under Prioritization Logic, choose Define Sort Values.
• Add the following values, and press Enter or click Add after typing each value:
  • Running Jacket
  • VLTG Bomber Jacket
  • Bose Bluetooth Earphones

DESelect will now give priority to the records with Product Name Running Jacket, followed by VLTG Bomber Jacket and lastly Bose Bluetooth Earphones. If there are no Order Lines for any of these products for a specific customer (ContactId), a random Order Line will be chosen.

Press Confirm to save your prio deduplication settings. 

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown with fields

• ContactId from DESELECT_DEMO_Orders
• OrderId from DESELECT_DEMO_OrderLines
• Name from DESELECT_DEMO_Products

The results table shows only one OrderLine per ContactId, with the preferred products if available.