Transformations and Properties
On This Page
Refer to this page for a description of each transformation, sample outputs, and any special conditions to keep in mind while configuring the filters and settings.
Tips
- To select multiple fields or Events, select the IN operator and separate the values with a comma.
Using Special Keywords in Transformations
The $self Keyword
Use the variable $self
to apply a formula on multiple fields on which the transformation is being applied.
For example, use the following filters to update the value of the price
field:
Filters:
Event Type = deals
, and Event Field isactive
= false
.
Fields = All fields with names in price
.
Settings:
Enter value or formula = 1.2* $self
.
Result:
The Transformation is applied to the field as follows:
The $null Keyword
The $null
keyword can be used when you want to add fields or update existing field values with a null value. Since all null fields are dropped when Hevo ingests your data, you can create transformations with this keyword to include them again. Or, you may want to assign null values to fields based on some conditions rather than dropping them altogether.
The following sequence of two transformations illustrate the usage of the $null
field.
1. Change Field Value
For Event Type = shop
and Event Field = price
, change the value of price
to $null
. The field with its null value is not displayed in the test results.
2. Add a Field
Now, in the Events transformed in step 1 above, for Event Field = price
and Event Field Value Is Null
, add a field count
with Value, Out-of-Stock
. The transformation is applied successfully and can be verified in the results, confirming the behavior of the $null
keyword.
The settings appear as follows:
Result:
The final output of the two Transformations appears as:
CONTROL BLOCKS
If-Else
Description: | Add an If-Else block to branch the data based on conditions. You can then add respective transformation blocks for the True and False condition. If-Else differs from the normal filters, as you can create two transformations based on the If-Else evaluation. |
Filter By: | All Events, Event Type, Event Field. |
Configuration Settings: | None. |
Transformation Considerations: | - The If-Else block branches the code to apply the transformation. You cannot currently merge the branches subsequently, and have to write the transformation for both branches separately. - To delete the If-Else block, you must first delete its branches. ![]() |
Example:
If the author
is Galos, Mike, a new field status
with value Discontinued is added. Else, the field price
is renamed to discounted price
and its value is changed to 0.9*price. The $self
keyword is used to indicate the price
field.
Filters:
Results:
The results in both conditions are displayed below:
If True:
If False:
DATE FUNCTIONS
Change Datetime Field Values
Description: | Add or subtract the specified amount of time from the given date value. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | Datetime Field: For example, Month. Offset Value: Use a negative value to subtract the time. For example, 1 to move the month forward by 1, or -2 to move the month back by 2. Read Datetime Field Values. |
Transformation Considerations: | - The date must be a valid date object. |
Example:
The behavior of this transformation is illustrated in two steps:
-
Transformation 1: Parse Date from String.
Filters:
Event Type =ad_campaigns
.
Fields = All fields with name =created_time
.This transformation changes the string time value
11:57:04
to time value1614167824000
(the millisecond representation of the Java date object). -
Transformation 2: Change Datetime Value
Filters:
Fields = All fields with name =created_time
.Settings:
Datetime Unit =Hour of day
andAmount
= 5.This increases the year value by 5 hours as
1614185824000
which translates to 16:57:04 in String format.
Format Date to String
Description: | Format a datetime value to a string value. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | String Format: - Use capital M to denote month (dd-MM-YYYY), and small m to denote minutes (HH-mm-ss). - Read DateTimeFormat for the list of supported patterns and formats. - Automatic detection of all date fields in an Event is not currently supported. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = ad_campaigns
.
Fields = All fields with name = created_time
.
Reult:
The value is converted into string format.
Parse Date from String
Description: | Parse a string value to a datetime object. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | String Format: The string format in which the date is currently stored. |
Transformation Considerations: | - Read DateTimeFormat for the list of supported patterns and formats. |
Example:
Filters:
Event Type = ad_insights
.
Fields = All fields with name = date_start
.
Settings:
String Format (in which the date exists) = dd-MM-YYYY
.
Reult:
The string value is converted to a date object.
EVENT MANIPULATION
Add a Field
Description: | Create a new field in the Event. |
Filter By: | All Events, Event Type, Event Field. |
Configuration Settings: | - Name of the Field. - Value of the Field. |
Transformation Considerations: | You can filter by only one Event Type and Property at a time. |
Example:
Filters:
Event Type = All Events.
Fields = All fields with name = Salary
whose value lesser than 60000
.
Settings:
New Field Name = Bonus
and New Field Value = 5000
.
Change Field Values
Description: | Replace a field value with another value. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | Enter formula or value: Provide the new value for the field(s). |
Transformation Considerations: | None. |
Example:
Filters:
Event Field = All Events with Field name = EmployeeID
whose value is in range (3,7) .
Fields = All fields with name = Salary
.
Settings:
Enter formula or value = $self + 5000
.
Drop Events
Description: | Filter Events based on a criteria. Matching Events are dropped from the table. |
Filter By: | All Events, Event Type, Event Field. |
Configuration Settings: | None. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = Employee
.
Fields = All fields with name = Salary
whose value greater than 50000
.
Settings:
None.
Result:
The Event is dropped and a confirmation message is displayed during test.
Drop Fields
Description: | Filter column values based on a criteria. Matching field columns are dropped from the table. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | None. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = Employee
.
Fields = All fields with name = Current_Employee
.
Settings:
None.
Result: The selected field is dropped.
Find & Replace
Description: | Find and replace string values across one or more fields. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | - Find: The string to be searched. For example, Limited. - Replace: The value to replace the search string with. For example, Ltd. - Regex: Select the check box if you want the input to be matched using a regular expression. For example, “\D” to find all non-digit characters. - Match Case: Select the check box to match the casing of the string while searching. |
Transformation Considerations: | Find & Replace works on field values only and not on field or Event names. |
Example:
Filters:
Event Type = Student.Sheet1
.
Fields = All fields with name = Result
.
Settings:
Regex = selected. Find = “\d”. Replace = Fail.
Result:
Fail in Result
field is changed to F.
Rename Events
Description: | Rename one or more Events. |
Filter By: | All Events, Event Type, Event Field. |
Configuration Settings: | New Name: Specify the new name for the Event(s). |
Transformation Considerations: | You can rename only one Event at a time. |
Example:
Filters:
Event Type = Student.Sheet1
.
Fields = All fields with name = Result
whose value = Pass
.
Settings:
New Name = Student.Sheet1.Passed
.
Result:
The Event name is changed if Result
is Pass
.
Rename Fields
Description: | Rename one or more fields. |
Filter By: | All Events, Event Type, Event Field. |
Configuration Settings: | - Field Name: The existing name of the field. - New Name: The changed name of the field. |
Transformation Considerations: | You can rename only one field at a time. |
Example:
Filters:
Event Type = shop
and Event Field Name =category
and Event Field Value = Food
.
Settings:
Field Name = price
and New Name = original_price
.
Result: The name of the field is changed.
JSON MANIPULATION
Flatten JSON
Description: | Flatten a JSON object into individual fields. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | None. |
Transformation Considerations: | The fields must be in valid JSON format, else an error is displayed. |
Example:
Filters:
Event Type = catalog.books
.
Fields = All fields with name = category
.
Settings:
None.
Result:
Each value of the category
field is converted to a separate JSON field.
Parse JSON from String
Description: | Parse JSON string to a JSON object. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | None. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = demo.agents
.
Fields = All fields with name = category
Settings:
None.
Result:
The JSON string is changed to JSON fields.
MATHEMATICAL FUNCTIONS
Format Number to String
Description: | Format numbers to strings. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | String Format: This can be: - Plain Text (“123456”) - Number (1,23,456) - Percent (123.45 %) - Scientific (1.01E + 12) - Currency ($ 1,23,456.00). Currently, only USD is supported. - Custom |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = Employee
.
Fields = All fields with name = Salary
.
Settings:
String Format = Number
.
Result:
The biz_id value is changed to a number format.
Parse Number from String
Description: | Parse number from string value. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | None. |
Transformation Considerations: | Any commas and spaces are removed from the string. |
Example:
Filters:
Event Type = Employee
.
Fields = All fields with name = SSN
.
Settings:
None.
Result:
The value of the SSN
field is changed from String to Number format.
Round-off Decimal Fields
Description: | Round-off Decimal Values to lower precision. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | - Rounding Mode: This can be Round Up, Round Down, Round Half Up, Round Half Down, Celing and Floor. The rounding mode is applied in conjunction with the decimal scale. - Scale: The number of decimal places to retain after rounding. Not applicable for Ceiling and Floor modes. Read Rounding Modes. |
Transformation Considerations: | Scale is defined for all rounding modes except Ceiling and Floor. |
Example:
Filters:
Event Type = ad_insights_platform_device
and Event Field Name = cpm
.
Settings:
Rounding mode = Floor
.
Result:
The value of cpm
is rounded off using the Floor
rounding mode.
SECURITY-RELATED TRANSFORMATIONS
Hash Fields
Description: | Hash a sensitive field value. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | Hashing Algorithm: This can be MD5, SHA-256, SHA-512. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = All Events.
Fields = All fields with name = Marks
.
Settings:
Hashing Algorithm = SHA-256.
Result:
The value of the marks field is hashed using SHA-256 algorithm.
Mask Fields
Description: | Mask a sensitive field value. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | None. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = Student.Sheet1
.
Fields = All fields with name = Marks
.
Settings:
None.
Result:
The value of the field is masked.
STRING FUNCTIONS
Modify Text Casing
Description: | Convert string values to lowercase, uppercase, snake case, or title case. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | Text Case: This can be To Lower Case, To Upper Case, To Title Case, To Snake Case. Read Text Casing. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = Student.Sheet1
.
Fields = All fields with name = Result
.
Settings:
Text Case = Upper Case
.
Result:
The value of the field is changed to Upper case.
Split Fields
Description: | Split a field in to two or more fields. |
Filter By: | All Events, Event Type, Event Field. All Fields, Field Name, Field Value. |
Configuration Settings: | Delimiter: The regular expression used to find the string around which the field will should be split. - To specify a regular expression special character as delimiter, escape it using . For example to use $ as a delimiter in Donald$Duck specify \$ as the delimiter. - Trailing empty strings are discarded. For example, in the case of wonder_women_ with _ as delimiter, the output is "wonder", "women" instead of "wonder", "women", . Create fields from values: If selected, the returned array of strings after splitting is converted into separate fields in the Event. |
Transformation Considerations: | None. |
Example:
Filters:
Event Type = Student.Sheet1
.
Fields = All fields with name = Student Name
.
Settings:
Delimiter = Comma. Create fields from values = Select this to create a new field for each value.
The settings appear as follows:
Results:
The output is modified as follows if the Create fields from value check box is selected:
The output is modified as follows if the Create fields from value check box is NOT selected:
Revision History
Refer to the following table for the list of key updates made to this page:
Date | Release | Description of Change |
---|---|---|
Apr-25-2022 | NA | Updates the screenshots to reflect the latest UI. |