Use the mapping formula editor to apply formulas directly in your mapping fields. Using functions in your mapping formulas, such as text and date functions, allows you to perform more complex manipulations to your data.
For example, applying the COALESCE() function when there’s a change in your file column headers. (csv[‘Media Buy Key’]) —file name is uppercase, to (csv[‘media buy key’]) —file name is lowercase. CSV fields are case-sensitive, the new header doesn’t match the name specified in the mapping, causing the new file process to fail.
After applying this formula COALESCE([csv['Media Buy Key'], csv['media buy key']]), reprocess the data stream—changes are applied allowing any failed files to go through successfully.
The formulas are intuitive and use a similar syntax as Excel. More complex manipulations can be done with MVEL, a Java-based expression language.
- VLOOKUP
Use the VLOOKUP function to retrieve specific data from a table. You can use it when you’re mapping fields from your source file to Marketing Cloud Intelligence. - FUZZYVLOOKUP
The FUZZYVLOOKUP mapping formula helps identify two elements of text, strings, or entries that are similar but not identical. - PARSEDATE and FORMATDATE Functions
You can convert a date to text format, for example converting a date to a string format. The conversion is called formatting and you can use the FORMATDATE function. You can also do the opposite—convert a string into a date format to obtain a date object, the conversion is called parsing and the PARSEDATE function is used - REPLACE
Use the REPLACE function to replace characters or patterns in a string. - GROUPCONCAT
Use the GROUPCONCAT function to merge data from multiple rows into one field. GROUPCONCAT is a GROUP BY function and returns a string if the group contains at least 1 non-null value, if no non-null values exist, it returns a null value. - Using Special Characters in Java Regular Expressions
In Marketing Cloud Intelligence, you can use the Java Regular Expression (RegEx) API to search for and modify text strings, for example, in a mapping formula or a calculated dimension. RegEx API uses special characters called metacharacters. A metacharacter is a character that has a special meaning during pattern processing. You can use a metacharacter as a regular character by escaping.