Values and Formulas

Last updated on Dec 15, 2022
On This Page

Note: This article applies to Drag-and-Drop Transformations only.

Refer to the following guidelines while using values and formulas to create simple and complex Transformations in Hevo:

Points to Consider

  • Arbitrary expressions can be used. For example, int_col + dec_col * 1000 + STRING_TO_NUMBER(REPLACE(id, "-"," "), 1)

  • Constants

    • Everything within “double quotes” is considered a string constant.

    • Integers and decimals are supported

  • References

    • Fields from the Events can be specified by using the field names. If a nested field is referenced, each level of nesting must be separated using a .(dot). For example, location.state.name.

    • Identity field may be indicated using $self. This can be used when the field selection is on a condition and several fields may match the condition.

    • A null value may be indicated using the $null keyword.

  • Operators may be used to combine the expressions.

  • Functions may be used in the expressions.

Applicable Operators

The following operators are supported:

- +, - , *, /, ==,!=,!

Applicable Functions

Function Name Description Parameters
LOWER Convert string to lowercase. Value or formula that returns a string.
UPPER Convert string to uppercase. Value or formula that returns a string.
TITLE Convert string to title case. Value or formula that returns a string.
SNAKE_CASE Convert string to snake case. Value or formula that returns a string.
REPLACE Find an expression in the given string and replace it with the provided value. - Regex: “REGEX” if the expression to find is a regular expression, else, “OTHERS”.

- Match case: “MATCH_EXACT” if match exact, “OTHERS for case insensitive.

- Find: Expression to find.

- Replace: Value to replace.
SPLIT_FIELDS Split a field using a delimiter and create new fields from it. Delimiter to split on.
SPLIT_ARRAY Split a field using a delimiter and convert the values into an array. Delimiter to split on.
JSON_TO_STRING Convert a JSON object to a string. Value or formula that returns a JSON object.
PARSE_JSON Parse a JSON object from a string. Value or formula that returns a string.
FLATTEN Flatten a JSON object. Value or formula that returns a JSON object.
NOW Returns the current Datetime. None.
DATE_TO_STRING Convert a datetime field to a string based on the specified format. - Value or formula that returns a datetime field.

- Output string format.
PARSE_DATE_STRING Convert a string to a datetime field based on the specified pattern. - Value or formula that returns a string.

- Input string format to identify datetime properties and create the datetime field from.
DATE_ADD Adds or subtract given time period from a datetime field. - Value or formula that returns a datetime field.

- Datetime unit to modify.

- Amount, in number, to be added or subtracted. Use negative value for subtraction.
NUMBER_TO_STRING Convert a number(integer/decimal) to string based on the given parameter. - Value or formula that returns a number.
Output string pattern can be one of the following:
- “PLAIN”
- “NUMBER”
- “PERCENT”
- “SCIENTIFIC”
- “CURRENCY”
- “Custom pattern”.
STRING_TO_NUMBER Convert a given string to a number. Value or formula that returns a string that can be parsed into a number.
ROUND_NUMBER Round the given decimal value. - Value or formula that returns a number (integer or decimal).

- Rounding Mode
* “UP”
* “DOWN”
* “HALF_UP”
* “HALF_DOWN”
* “CEILING”
* “FLOOR”

- Scale: Number specifying the scale.
Required if Rounding Mode is one of “UP”, “DOWN”, “HALF_UP”, or “HALF_DOWN”
HASH Hash a given value or formula using the given strategy. - Value or formula.

- Hash Strategy:
* “SHA-256”
* “SHA-512”
* “MD5”
MASK Masks the given value or formula. Value or formula.
RENAME Renames the given field to a new value. - Field name or path to the field name to be renamed.

- New field name.
Was this page helpful?

Tell us what went wrong