How to use multiple fields as the unique field in deduplication?

Scenario: You want to deduplicate the records of a Data Extension. As the unique field, you would like to include multiple fields. 

Note: This scenario uses the feature Custom Value: SQL functions. This feature is only enabled for DESelect Advanced.

Let's assume you have a Data Extension called Orders, with the following records:

You would like to keep only unique combinations of person-product pairs. Ideally, the one with the highest quote, the newest version, and the latest Date. In this case, you would like to keep the last two rows.

Let's see how we can achieve this through DESelect!

1. Selection Criteria

• Drag and drop your Orders Data Extension to your Selected Data Extensions.

2. Target Definition

The first thing we need to do for our Target Data Extension is to create a field to use in deduplication. Since we need unique combinations of Person and Product we will create a custom value that combines these two fields.

• Click on Create Data Extension button.
• Provide the name Deduplicated DE on multiple fields and click Save.
• Click on Add new value inside the Custom Values area.
• Set the name to Id.
• Select Apply formula to a field.
• Select Apply any function.
• Under the Insert field section, select Orders Data Extension and Person field.
• Click on the Insert field button.
• In the formula section add a white space, a plus icon (+), and one more white space.
• Under the Insert field section, select Orders Data Extension and Product field.
• Click on the Insert field button. Your formula should now look as follows:
  
• Click on the Save button.
• Add the Id custom value to your Target Data Extension's fields.
• Click on the Add All Fields button of your Orders Data Extension, under the Available Fields section.

Now that we created our Target Data Extension, we can set the deduplication rules, and deduplicate using the custom value we created as the unique field. Then, we can proceed with defining the priority criteria, as we would normally do.

• Click on Save Data Extension and hit the Create button on the pop-up.
• Click on the gear icon on the right and select Prio Deduplication.
• Select Id as the unique field.
• Click on Next.
• Select Quote from the dropdown and sort all values from highest to lowest.
• Click on Add Sorting Option.
• Select Version from the dropdown and sort all values from highest to lowest.
• Click on Add Sorting Option.
• Select Date from the dropdown and sort all values from highest to lowest. Your deduplication logic should look similar to the image below:
  
• Click Confirm.

3. Preview

Click on the Run Preview button, you should see the following results:

Conclusion

When you want to remove duplicates using a combination of fields, you can use the Custom Values feature. With Custom Values, you can combine the fields into a single value, to use later on in Prio Deduplication.