13. Get the Order Line with the highest Quantity for each Product (basic deduplication)

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

Scenario: Products are ordered many times (stored in Order Lines). You want to pick the Order Line with the highest Quantity for each Product, so you can see the biggest quantity of each Product that has been ordered in.

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

1. Selection Criteria

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

Drag data extension DESELECT_DEMO_Order_Lines to the Selected Data Extensions section.

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. Target Data Extension

Hit the Create Data Extension button, enter a name, eg. Products with the highest quantity Order Line, and press Save.

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

• OrderId and Quantity 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 Name as the unique field (this is the product name)
• Press Next
• Choose Prio Deduplication Mode Basic
• As the Field that determines the priority, choose Quantity
• Under Prioritization Logic, choose Sort All Values
• Choose Highest under Value For Quantity

DESelect will now give priority to the record with the highest value for Quantity for each Product Name.

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

• OrderId and Quantity from DESELECT_DEMO_OrderLines
• Name from DESELECT_DEMO_Products

The results table shows only one OrderLine per Product, the one with the highest Quantity for that Product.