15. Get the preferred Order Line based on complex rules (advanced deduplication)

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:

- any product in the Jackets Product Family if there is an Order Line for this product
- product Huawei P20 Lite if there is an Order Line for this product and no Order Line for products in the Jackets Product Family

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

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, Product Family 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 Advanced.
• Click on Add Rule.
  
  • First Rule:
    
    • Drag Product Family from the Available Fields section to the Filters section
    • Choose equals as the criteria
    • Enter Jackets as the value
    • Hit Save
      --> we've now created our first rule
• Click on Add Rule.
  • Second Rule:
    • Drag Name from the Available Fields section to the Filters section
    • Choose equals as the criteria
    • Enter Huawei P20 Lite as the value
    • Hit Save--> we've now created our second rule

DESelect will now give priority to the records Product Family Jackets, followed by records with Product name Huawei P20 Lite. If there are no Order Lines matching any of our defined rules 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, Product Family from DESELECT_DEMO_Products

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