Augment Transformation Offers More Support for Multiple Matches

Control the augment transformation’s behavior in a new way when it encounters multiple matches. Previously, Analytics Cloud returned results from all matched rows. Now, the augment transformation can return results from one of the matched rows—which is the new default behavior—or from all matched rows.

For each record in the left dataset, the augment transformation performs a lookup to find a matching record in the right dataset. If the left dataset has a one-to-many relationship with the right dataset, Analytics Cloud might find multiple matches for a left record. How Analytics Cloud handles multiple matches depends on the specified augment operation. You can specify the LookupSingleValue or LookupMultiValue operation in the augment transformation. If you don’t specify an operation, Analytics Cloud treats the augment as if you specified the LookupSingleValue operation.

Note

Note

The default behavior changes in this release. Analytics Cloud might produce different results based on the new default behavior. To revert back to the default behavior of the previous release, set the operation to LookupMultiValue.

LookupSingleValue Operation

Use the LookupSingleValue operation to force the augment transformation to return results from a single row when multiple matches are found. Analytics Cloud randomly selects one row from the list of matched rows.

Note

Note

Each time you run the dataflow, Analytics Cloud can return a different result depending on the returned row.

For example, you apply the augment transformation on the following datasets, set the relationship to "Price," and match them based on the Id and ProdId fields.
There are two input datasets, where the right dataset (Account) has two records with the same account ID.
Although there are multiple rows for Prod3, Analytics Cloud randomly chooses one matching row and returns values based on that row. Here’s the resulting dataset after the augment if Analytics Cloud chooses the first Prod3 row.
The resulting dataset contains one record with results from one right dataset record only.

LookupMultiValue Operation

Use the LookupMultiValue operation to force the augment transformation to return results from all matching rows when multiple matches are found. When multiple matches are found, Analytics Cloud creates multiple dimension values and sums measure values in the resulting dataset record. For example, you apply the augment transformation on the following datasets, set the relationship to "Price," and match them based on the Id and ProdId fields.
There are two input datasets, where the right dataset (Account) has two records with the same account ID.
Because the lookup returns multiple rows for Prod3, the Price.Pricebook field in the resulting dataset appears twice, once for each match. The measure field Price.UnitPrice contains 1500, which is the sum of 800 and 700. Here’s the resulting dataset after the augment.
The resulting dataset contains one record with multiple dimension values and a sum of the annual revenue measure values.

Operation Example

You specify the operation in the augment transformation as shown in the following dataflow snippet.
"Augment_OpportunitiesWithAccountDetails": {
   "action": "augment",
   "parameters": {
      "operation": "LookupSingleValue",
      "left": "Extract_Opportunity",
      "left_key": [ "AccountId" ],
      "relationship": "OpptyAcct",
      "right": "Extract_AccountDetails",
      "right_key": [ "Id" ],
      "right_select": [
         "Name",
         "BillingAddress"
      ]
   }
}