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 #

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
  

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