Expression Syntax Reference
The advanced datapoint controls of our tool allow the user to define complex relationships between the values of fields within the button matrix, and the text strings which are outputted in the URL. Every field in the button matrix has a value, which takes the form of a text string. Typically, this value will be associated with whatever button in the field has been pressed, but this value may also be the text which has been typed into a free text entry field.
This text string does not have to appear in the output url as is. Instead, the text output of a field can be manipulated in a number of ways, via a range of functions. The syntax and behaviour of these functions is detailed below.
NOTE: The behaviours detailed below can only be accessed when the customisation area is in ‘advanced’ mode.
SWITCH:
SWITCH( expression, search, result [, search, result ]... ,default )A SWITCH expression replaces a specific value with either another specific value, or a default value, depending on the result of an equality condition. This operation is equivalent to the operation of an IF-THEN-ELSE statement.
Expression: The source of the value that you want to compare, such as the value of a field in your convention.
Search: The target value that is compared against the source expression. You need to specify separate search/result pairs for each value that you want to replace.
Result: The replacement value that query returns when the expression matches the search value. You must include at least one search/result pair in the SWITCH expression.
Default: A default value that is used for cases when the search condition fails. If you do not specify a default value, the SWITCH expression returns ERROR as a warning.
Example
SWITCH(field:source,
fb, facebook,
ig, instagram,
other
)This function is based on the DECODE function used by Amazon Redshift: https://docs.aws.amazon.com/redshift/latest/dg/r_DECODE_expression.html
______
IF:
IF( boolean {true | false}, trueResult, falseResult )Parameters
Boolean: A boolean is either the value true, or the value false. Any other value given will be treated as false.
trueResult: the value outputted if the boolean is true
falseResult: the value outputted if the boolean not true
Example
IF(true,
great-stuff,
bad-stuff
)
output --> great-stuff
)EQUALS:
EQUALS(a, b)
Parameters
a & b are any valid values to be compared
Example 1:
Context: a Field named source, is set to the value "facebook"
EQUALS(${source}, facebook)
output --> true
Example 2:
IF( EQUALS(facebook, facebook) , great-stuff, bad-stuff)
output --> great-stuffENCODE:
ENCODE(value)
ENCODE turns any value to a URL compatible value. Encodes a URI by replacing each instance of certain characters by one, two, three, or four escape sequences representing the UTF-8 encoding of the character (will only be four escape sequences for characters composed of two “surrogate” characters).
ENCODE( url safe/)
Output → url%20safe%2F
DECODE:
DECODE(value)Decodes a Uniform Resource Identifier (URI) component previously created by ENCODE or by a similar routine.
Example:
DECODE( url%20safe%2F )
Output → url safe/
DATE:
DATE( timeDeltaOrDate )Parameters
timeDeltaOrDate can accept a date string (YYYY-MM-DD).
timeDeltaOrDate as number – will provide the date x days forward from TODAY
ExampleDATE(0) ---> 2020-02-01 (if today is the 1st of Feb 2020)
DATE(2) ---> 2020-02-03 (if today is the 1st of Feb 2020)
DATE(2020-02-01) ---> 2020-02-01 (if today is the 1st of Feb 2020)
MID:
MID(inputValue, startIndex, endIndex)Parameters
inputValue: the string to take the middle section of
startIndex: the first letter to take (starting from 0)
endIndex: the last letter to take (starting from 0)
Example
MID( cleaner,1,5) ---> lean
MID( cleaner,0,3) ---> clea
LEFT:
LEFT(inputValue, numOfCharacters)
Take the leftmost x characters from a string
Parameters
inputValue: the string to take characters from
numOfCharacters: the number of characters to take
Example
LEFT(google-cpc , 6) -> google
RIGHT:
RIGHT(inputValue, numOfCharactersFromRight)same as LEFT but characters are taken from the RIGHT
REPLACE:
REPLACE(string1, old_chars, new_chars)Parameters
String: A string of text characters to be searched
Old_chars: A string of characters within the string, to be replaced.
New_chars: A new string of characters to replace the old_string.
Example
REPLACE(fb-cpc, fb, facebook) --> facebook-cpc
REGREPLACE:
REGREPLACE(string1, regularExpression, new_chars)
Parameters
String: A string of text characters to be searched
regularExpression: a string representing a regular expression to match against the string. Any matches will be replaced.
New_chars: A new string of characters, to replace the regularExpression..
Example
REGREPLACE(facebook-cpc, o+|c , x) --> faxebxk-xpx
COALESCE
COALESCE ( expression, expression, ... )The COALESCE expression returns the value of the first expression in the list that is not null, or hidden. If a field is hidden in the button matrix/chrome extension (as a result of a field logic behaviour), then the field will output the string ‘hidden’. If all expressions are null/hidden, the result is null. When a non-null value is found, the remaining expressions in the list are not evaluated. A typical use of this function is to list a number of fields. The value of the first visible field will be the output of the function.
Parameters
Expression: Any string of text data. In our case, it will often be the output of a field.
Example
If the field “usa-cities” is hidden and “uk-cities” has the value “london”
COALESCE ( ${usa-cities}, ${uk-cities}, na) --> londonDAYOFWEEK
As per Date, but return day named
TODAY() = DATE(0)
MONTH()
As per Date, but the input brings you months forward and the result is the first day of the month.
WEEK()
see above
CALWEEK()
like DAYOFWEEK
HASH | MD5:
HASH | MD5 (value)
return MD5 hash of a value
SPLITPART
SPLITPART(value,delimiter,part)This function is based on the Amazon Redshift function of similar name:
https://docs.aws.amazon.com/redshift/latest/dg/SPLIT_PART.html
Parameters
String: The string of text to be split.
Delimiter: The delimiter string.
Part: Position of the portion to return (counting from 0). If part is larger than the number of string portions, SPLITPART returns an empty string. If delimiter is not found in string, then the returned value contains the contents of the specified part, which might be the entire string or an empty value.