In Tableau CRM we have a compute relative node in dataflow where we can get Current, First, Next, Previous values of a grouping/partition and perform operations.
In order to achieve the same Compute Relative Functionality in Recipe we need to follow the below steps:
Example
Create a dataset using a recipe to show the recently created contact of an account. In order to achieve this in dataflow we can use a compute relative node. We can achieve same in recipe by following these steps:
Create a Recipe
👉 Create a recipe and add input data Contact object from SFDC_LOCAL connection. Select the columns from the contact object.
Join Related Object
👉 Click on the Related Objects tab and join the Account Object using Account Id.
Add Transform Node
👉 Click on Custom Formula and switch on Multiple row formula.
👉 Scroll down and add Partition By Account ID and Order By Created Date of contact. Select Sort Direction as Descending and Output Type as Number with default value as 0. Show results in the New Column.
Enter the formula
👉 In Window functions you have
First_Value – Returns the value from the First record in the partition.
Lag – Returns the value from the Previous record in the partition.
Current – Returns the value from the Current record in the partition.
Lead – Returns the value from the Next record in the partition.
Last_Value – Returns the value from the Last record in the partition.
👉 Functions to get the first value, previous value, current value, next value and last value of a grouping/partition respectively.
👉 Enter the formula in formula editor
case when first_value(Id) = current(Id) then 1 else 0 end
👉 Apply the changes.
❝Here Id refers to contact id.❞
Here first_value(Id) returns the contact id from the first contact record in grouping/partition.
Here current(Id) returns the contact id from the current contact record in grouping/partition.
👉 It checks for the matching of contact id and returns 1 if matched else 0 and as it is sorted by created date of contact in descending order, we get the recently created contact record of an account. New formula field First Value is created and results are stored in the field.
Add Filter Node
👉 Filter the data with the First Value field equals to 1. Data is filtered with only recently created contact for each account.
Add Output Node
👉 Write the results to a dataset. Name of the Dataset here is Latest Contacts. Save and run the recipe.
👉 After successfully running the recipe in Jobs Monitor explore the dataset.
Explore the dataset
👉 Lens shows only the recently created contact for each account.
📝 By following the above steps you can achieve compute relative functionality in the recipe in Tableau CRM.
