Note: The scenario below uses data from the DESelect Demo Data Package.
Scenario: You have a data extension (Orders) that contains a column with order dates and you want to add a new field to your target data extension that calculates the number of days between the order dates and the current date.
Note: This scenario uses feature Custom Values, which is available in DESelect Plus and Advanced.
1. Selection Criteria
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.
2. Target Data Extension
Hit the Create Data Extension button, enter a name, e.g. Orders Target, and press Save.
Select the following fields by either double-clicking on them or dragging them to the section on the right:
- ContactId, Date and Amount from DESELECT_DEMO_Orders.
- Rename Amount field to Days Since Order.
Click Save Data Extension.
Click on the Delete icon next to the Date field that's mapped to Days Since Order.
We have now created an extra field in our target data extension that we want to populate with the number of days between the date the order was made and the current date. This field is not mapped to any fields in the source data extensions.
Now we're going to add a Custom Value:
- Under Custom Values on the left, click Add new value.
- Under Name, enter: Days Since Order and hit Next.
- Choose Type: Apply formula to a field.
- Choose Type: Date Difference.
- Add first date:
- Below Date 1 label, make sure Field option is selected.
- On the drop-down list labeled Data Extension select DESELECT_DEMO_Orders.
- On the drop-down list labeled Field select Date.
- Add first date:
- Below the Date 2 label, make sure Timestamp option is selected.
- Select Date radio button.
- Select days in the Express difference in drop-down list.
- Hit Save.
The logic defined above will, for each row, calculate the difference between the value of Date column and the current date in days and return the result.
Now that the custom value Days Since Order has been created, we can map it to the field Days Since Order by drag-and-dropping it from Custom Values to the Days Since Order mapping field.
When you click Run Preview on the Preview screen, a results table will be shown with fields
- ContactId and Date from DESELECT_DEMO_Orders
- Days Since Order, populated with the number of days between the date of Date field and the current date.