Loading

Appending Datasets with mismatched field names in CRM Analytics

Date de publication: Jul 17, 2023
Description
The append Transformation cannot match fields with different names by default. "Allow disjoint schema" will allow the Append to complete without errors but with partially null columns for mismatched fields: see the "Append Datasets with Different Schema" section of the Append documentation for details.

It is sometimes necessary to append two datasets with disparate naming structures and have the resulting fields be unified. A common use case is appending a dataset from an internal source with a dataset from an external source. Field names in a dataset generated from an internal source have different naming restrictions than field names in a dataset generated from an external source. The external data field naming conventions are more restrictive than those generated internally.

The most notable difference is the "." in internally generated fields with a relationship defined from an augment Transformation. External data cannot contain "." in field names.
Résolution
In order to successfully Append data from disparate sources, the field names must be changed to match.

This can be done in two ways:
  • In a Recipe: fields can be mapped during the Append process. The data structure manipulation is handled automatically behind the scenes.
  • In a Dataflow using computeExpression and sliceDataset: create new fields with the matching names then remove the "old" fields.
The computeExpression -> sliceDataset transformation sequence can be created in the UI Dataflow Editor as well as directly in the dataflow JSON definition. This example JSON creates a field called "Account_Name" with the same values as "Account.Name" and then removes "Account.Name" from the dataset. The Append would occur after this set of transformations: 
{
  "RenameInternalFields": {
    "action": "computeExpression",
    "parameters": {
      "source": "internalSourceNode",
      "mergeWithSource": true,
      "computedFields": [
        {
        "name": "Account_Name",
        "type": "Text",
        "label": "Account Name",
        "saqlExpression": "Account.Name"
        }
      ]
    }
  }
},
{
  "RemoveMismatchedInternalFields": {
    "action": "sliceDataset",
    "parameters": {
      "source": "RenameInternalFields",
      "mode": "drop",
      "fields": [
        {
        "name": "Account.Name"
        }
      ]
    }
  }
}
Numéro d’article de la base de connaissances

000381265

 
Chargement
Salesforce Help | Article