Available Transformations #

Transforming data involves applying a function to a series of values to replace them with new ones. With Prisma Campaigns, you can change the format, structure, or values. As a result, data becomes better organized for reporting and analytics. Also, this process makes compatibility with other applications easier and protects against null values or other pitfalls.

Sample Dataset #

Although most of the examples below are presented with the sample data, it is important to clarify that transformations can be applied not only to specific values but also to variables like {{customer.name}}, for example.

Conversion Functions #

• as_int() converts a value to an integer number

  Balance_USD
  => "1103"
  
  Balance_USD:as_int()
  => 1103
  

• as_decimal() converts a value to a decimal number

  Currency_Rate
  => "41.13"
  
  Currency_Rate:as_decimal()
  => 41.13
  

• as_date() converts a value to a date and time

  Issue_Date
  => "2020-04-01"
  
  // UTC timezone
  Issue_Date:as_date()
  => #inst"2020-04-01T00:00:00.000-00:00"
  
  // specific timezone
  Issue_Date:as_date("America/Los_Angeles")
  => #inst"2020-04-01T07:00:00.000-00:00"
  
  // specific GMT offset
  Issue_Date:as_date(-7)
  => #inst"2020-04-01T07:00:00.000-00:00"
  
  // using a custom format
  Exp_Date:as_date(-7, \"MM/dd/YYYY\")
  => #inst"2024-04-01T07:00:00.000-00:00"
  
  // some formats are detected automatically
  Exp_Date:as_date(-7)
  => #inst"2024-04-01T07:00:00.000-00:00"
  

Arithmetic Operators #

• Addition/Concatenation

  Balance_USD:as_int() + Balance_USD:as_int()
  => 2206
  
  Balance_USD:as_int() + -1000
  => 103
  

  When applied on strings, it concatenates them.

  City + ", " + Country
  => "Atlanta, USA"
  

• Subtraction, multiplication and division

  Balance_USD:as_int() - Balance_USD:as_int()
  => 0
  
  Balance_USD:as_int() * -1
  => -1103
  
  Balance_USD:as_int() * 2
  => 2206
  
  Balance_USD:as_int() * Currency_Rate:as_decimal()
  => 45366.39
  
  Balance_USD:as_int() / 2
  => 551.5
  
  Balance_USD:as_int() / Balance_USD:as_int()
  => 1
  

Date Functions #

Most of these functions accept a language parameter ("en" for English and "es" for Spanish) to return values in that tongue.

• system:current-date() returns the current system date

  system:current-date():format-date("yyyy-MM-dd")
  => "2022-12-03"
  

• day() gets the day of the month

  Issue_Date:as_date():day()
  => 1
  

• dayname() and Dayname() return the day of the week lowercased and capitalized, respectively:

  Issue_Date:as_date():dayname("en")
  => "wednesday"
  
  Issue_Date:as_date():dayname("es")
  => "miércoles"
  
  Issue_Date:as_date():Dayname("en")
  => "Wednesday"
  
  Issue_Date:as_date():Dayname("es")
  => "Miércoles"
  

• month() gets the month of the year

  Issue_Date:as_date():month()
  => 4
  

• monthname() and Monthname() return the name of the month lowercased and capitalized, respectively:

  Issue_Date:as_date():monthname("en")
  => "april"
  
  Issue_Date:as_date():monthname("es")
  => "abril"
  
  Issue_Date:as_date():Monthname("en")
  => "April"
  
  Issue_Date:as_date():Monthname("es")
  => "Abril"
  

• year() gets the year

  Issue_Date:as_date():year()
  => 2020
  

• formal() and Formal() return the date in a formal format

  Issue_Date:as_date():formal("en")
  => "wednesday, april 1, 2020"
  
  Issue_Date:as_date():formal("es")
  => "miércoles 1 de abril de 2020"
  
  Issue_Date:as_date():Formal("en")
  => "Wednesday, April 1, 2020"
  
  Issue_Date:as_date():Formal("es")
  => "Miércoles 1 de Abril de 2020"
  

• format-date() converts the date into a custom format

  Issue_Date:as_date():format-date("M/d/YYYY")
  => "4/1/2020"
  
  Issue_Date:as_date():format-date("EEE, d MMM yyyy")
  => "Wed, 1 Apr 2020"
  

• years-from-date() gets the number of years from a date

  Issue_Date:as_date():years-from-date()
  => 2
  

• addDays(N) adds N days to a date

  Issue_Date:as_date():addDays(30):format-date("yyyy-MM-dd")
  => "2020-05-01"
  Issue_Date:as_date():addDays(30):format-date("EEE, dd MMM yyyy")
  => "Fri, 01 May 2020"
  Issue_Date:as_date():addDays(30):format-date("yyyy/MM/dd")
  => "2020/05/01"
  Issue_Date:as_date():addDays(30):format-date("dd/MM/yyyy")
  => "01/05/2020"
  Issue_Date:as_date():addDays(30):format-date("MM/dd/yyyy")
  => "05/01/2020"
  Issue_Date:as_date():addDays(30):format-date("dd MMM yyyy")
  => "01 May 2020"
  Issue_Date:as_date():addDays(30):format-date("MMM dd, yyyy")
  => "May 01, 2020"
  

Other Functions #

• or() returns a default value if the argument is empty

  Country:or("Default country")
  => "USA"
  
  Extra_Data:or("No extra information provided")
  => "No extra information provided"
  

• trim() Trim leading and trailing spaces

  More_Data:trim()
  => "MORE AND MORE DATA"
  

• capitalize() Capitalize each word on the string, making the first letter of each word upper-case and the rest of them lower case.

  More_Data:capitalize()
  => "     More And More Data "
  
  More_Data:trim():capitalize()
  => "More And More Data"
  

• replace(findStr, replaceStr) replaces all occurences of findStr with replaceStr.

  Email_Address:replace("@", " (at) ")
  => "chorch (at) gmail.com"
  

• ucase() converts strings to uppercase

  Country_Code:ucase()
  => "US"
  

• lcase() converts strings to lowercase

  City:lcase()
  => "atlanta"
  

• subs(pos, [len]) extracts a substring (len is optional). When len is not present, subs(pos) extracts the text beginning at pos to the end of the string.

  City:subs(0, 1) + City:subs(-3) + \".\"
  => "Mdeo."
  

• unescape-html() unescapes HTML characters (&, <, >, ", ', ', / and /)

  Sec_Question
  => "Some question"
  
  Sec_Question:unescape-html()
  => "Some question"
  

• getAt(index, separator) splits a string into an array using separator and returns the element at the position given by index (zero based).

  Email_Address:getAt(0, "@")
  => "chorch"
  
  Email_Address:getAt(1, "@")
  => "gmail.com"
  

• json(key) parses JSON to get a key

  JsonData:json("a_string")
  => "Json String Value"
  
  JsonData:json("a_boolean")
  => true
  

• format(formatStr) formats string using formatStr

  Name:format("Hello, %s!")
  => "Hello, John Doe!"
  
  Name:format("Hello, %s! ") + Balance_USD:as_decimal():format("Your balance is $%.2f")
  => "Hello, John Doe! Your balance is $1103.00"
  

Conditionals #

Simple conditionals are supported. The following example can be used to fill a computed field named Country based on the cellphone number (column: Cellphone).

IF Cellphone:subs(0, 2) = "+1" THEN
    "USA"
ELSE IF Cellphone:subs(0, 3) = "+44" THEN
    "UK"
ELSE
    "Other"
END

Using Variables #

You can use variables in transformations. For example, given a customer with the following data:

You can:

• Capitalize the first letter of each word in the name:

{{customer.full_name:capitalize()}}
=> "Gabriel Alejandro Cánepa"

• Transform the birth date to a more readable format:

{{customer.birth_date:as_date():format-date("EEE, d MMM yyyy")}}
=> "Wed, 11 Apr 1990"

• Get the first name:

{{customer.full_name:getAt(0, " ")}}
=> "gabriel"

• Concatenate the capitalized full name, city, and country:

{{customer.full_name:capitalize()}} lives in {{customer.city:capitalize()}}, {{customer.country:capitalize()}}
=> "Gabriel Alejandro Cánepa lives in Montevideo, Uruguay"