IIF() function in Data Prep mapping UI Calculated Field not populating variables as expected.

[williamh3207805]

Hello,

My XDM schema has a "userId" field that should receive one of three possible ids in the source data payload, based on which source id is available, using the Calculated Fields IIF() function.  So if "sourceId1" is available, it should always be used - if "sourceId1" is not available, "sourceId2" should be used, and if both those are not available, "sourceId3" should be used.  We are sending data to AEP via the Edge Network Server API and mapping source properties to our schema via the data prep mapping UI.

Source data is structured like this:

{
  "xdm": {
    "_myorg":
      {
        "sourceId1": 'aaa',
        "sourceId2": 'bbb',
        "sourceId3": 'ccc'
      }
  }
}

The IIF() statement I am trying to use is like this, mapping the result to "userId".

iif(${xdm\._myorg\.sourceId1} == '' || !${xdm\._myorg\.sourceId1} , (iif(${xdm\._myorg\.sourceId2} == '' || !${xdm\._myorg\.sourceId2} , ${xdm\._myorg\.sourceId3}, ${xdm\._myorg\.sourceId2})), ${xdm\._myorg\.sourceId1})

Even when the source properties are definitely in the source payload, data ingestion fails with the following error:

DCVS-1106-400

30

The message cannot be validated because a required property is missing: #/_myorg/mySchema: required key [userId] not found.

I get the same error even when using a simpler statement that removes the nesting:

iif(${xdm\._myorg\.sourceId1} == '' || !${xdm\._myorg\.sourceId1} ,${xdm\._myorg\.sourceId2}, ${xdm\._myorg\.sourceId1})

I also tried removing the ${} notation and writing out field names like "xdm._myorg.sourceId1" but that didn't work either.

A regular, non-calculated field mapping of "xdm._myorg.sourceId1" to the destination "userId" works just fine, so it doesn't seem to be an issue with the actual data values.  All other regular mappings work as well. 

Would anyone have some suggestions as to why the above logic causes the mapping to fail? We'd really like to have this conditional logic work on the AEP-side instead of having to update client code to figure out "userId" for us.

Thanks.

[mittalabhi86]

In my case, I never have to prefix the attribute path with “xdm”.

 Also, there are no slashes in the path.

 But since you said that this path is working in the non-calculated mapping, I’m surprised.

 When writing the expression, instead of manually writing the attribute name can you use the attributes tab on left menu?

i believe it might be that the entire path needs to go inside double quotes.

[mittalabhi86]

On giving it a second look, I noticed that you are using the attribute path from the schema, where as you should be using the path of the attribute in the incoming payload. The path that you shared seems to be the destination path and not the source path. Sorry if I misunderstood the question here.

[williamh3207805]

Thanks for the reply! Let me explain a couple things about my example above:
- We have the client send the source data in an "xdm" object - it's true that we don't strictly need to do this, it's just the convention we use for now.

- Accordingly, the source and destination paths are similar (and in my example above they end up being the same!) but in reality they are a little different.

We did build the mapping using the field menu on the left hand side.  I noticed that when I do this, the property name changes to blue (to indicate that the property exists in the schema, maybe?) See attached screenshot.

When we make edits to the path beyond what the field menu autopopulates, the blue highlighting disappears (does that mean it is no longer recognized as a property by the mapper?).

In any case, even wrapping the path in double quotes does not solve the issue, unfortunately. 
Double quotes also seems to go against the recommendation to use ${} here: https://experienceleague.adobe.com/docs/experience-platform/data-prep/functions.html?lang=en

[williamh3207805]

So yes, there seems to either be some simple formatting or setup step that we are missing or that is not documented clearly.  If anyone has any ideas, please let me know!

[williamh3207805 · Accepted solution]

After dissecting a few calls through assurance I figured out what was going on here.
So, according to documentation here: https://experienceleague.adobe.com/docs/experience-platform/data-prep/functions.html?lang=en
If you have a schema path with "." in it, it needs to be escaped like so: "\."
And in the Data Prep mapping UI, if you select fields from the left-hand toolbar to include them in an equation, it will helpfully auto-include the escape slash for you: eg ${xdm\._myOrg\.myData}.

But when I inspected calls via Assurance, I found that the Mapper tool was ALSO adding escape slashes, mean you ended up with a path like ${xdm\\._myOrg\\.myData}, which is invalid (see attached screenshot).
So, in order to solve this, I manually updated my mapping to remove the "\" escape character (again, which the Mapping UI adds automatically), so that my fields looked like ${xdm._myOrg.myData}.

If the mapper tool is already adding its own escape characters, it would be helpful to update the Mapping UI calculated field tool to not add an extra "\".