Data mapping rules and conditions
Data transformation rules in Data Octopus: text, numbers, type conversion, lookup, aggregation and filter conditions with examples.
General rules
Choose data
Choose an imported field or your own static value for this field.
Example: Rename "product_id" to "id_produktu".
Leave empty
Leave cells empty.
Example: Leave cells blank where product information is missing.
Text rules
Join values
Combine two or more values into one.
Example: Combine "first_name" and "last_name" into "full_name".
Add prefix
Add a prefix to each value in a column.
Example: Add "SKU-" to "product_code": "12345" → "SKU-12345".
Add suffix
Add a suffix to each value in a column.
Example: Add "-PL" to "country_code": "PL" → "PL-PL".
Replace single value
Replace one specific value with another.
Example: Replace "out_of_stock" with "unavailable" in status.
Replace multiple values
Replace several values with other values.
Example: Replace "yes" and "no" with "true" and "false" in "available".
Remove single value
Remove one specific value from a column.
Example: Remove "N/A" from "discount".
Remove multiple values
Remove several specified values from a column.
Example: Remove "unknown", "N/A" and "null" from "supplier".
Remove duplicates
Remove repeated values.
Example: Remove duplicate product entries from a product catalogue.
Recapitalize
Change the letter case of text.
Example: Change "laptop" to "Laptop" in "product_name".
Numeric rules
Recalculate Value
Recalculate values using new rules.
Example: Convert gross prices to net prices in "price".
Calculate Percentage Margin
Calculate percentage margin.
Example: Calculate percentage margin from "selling_price" and "cost_price".
Calculate Percentage Markup
Calculate percentage markup.
Example: Calculate percentage markup from "cost_price" and "selling_price".
Round Value
Round numbers to a specified number of decimal places.
Example: Round "price" to two decimal places.
Type conversion
Convert to text
Convert an array of values into one text string.
Example: Convert ["electronics", "gadgets"] to "electronics, gadgets".
Pick from array
Select one value from an array.
Lookup and extraction
Extract Text From
Extract specified data from a field.
Example: Extract a postal code from "address".
Lookup in Table
Full guide: Lookup in TableData aggregation
Segmentation
Divide data into equally sized groups. Select the sorting field in Order by, provide group labels in Tiles name (the number of labels determines the number of groups), and choose Descending or Ascending in Order direction.
Example: Divide products into three groups (high, mid, low) by ga4_item_netto_revenue in descending order.
Rank Values
Assign a rank to each row (product) using the criterion in Order By and Descending or Ascending in Order direction.
Example: rank products by gads_clicks in descending order.
Sum + date range
Full guide: Sum + date rangeConditions
is equal to
Check whether a value equals a specified value.
Example: Order status equals "shipped".
is not equal to
Check whether a value differs from a specified value.
Example: Product category is not "outlet".
contains
Check whether a value contains specified text.
Example: Product description contains "premium".
does not contain
Check whether a value does not contain specified text.
Example: Product review does not contain "bad".
is blank
Check whether a value is empty.
Example: "discount_code" is empty.
is not blank
Check whether a value is not empty.
Example: "email" is not empty.
starts with
Check whether a value begins with specified text.
Example: Product code begins with "ABC".
does not start with
Check whether a value does not begin with specified text.
Example: Category name does not begin with "old".
ends with
Check whether a value ends with specified text.
Example: Email ends with "@gmail.com".
does not end with
Check whether a value does not end with specified text.
Example: Phone number does not end with "000".
is greater than
Check whether a value exceeds a specified value.
Example: Product price is greater than PLN 100.
is greater or equal to
Check whether a value is greater than or equal to a specified value.
Example: Stock quantity is at least 10 items.
is less than
Check whether a value is less than a specified value.
Example: Delivery time is less than 5 days.
is less or equal to
Check whether a value is less than or equal to a specified value.
Example: Returns count is at most 2.
is between
Check whether a value falls between two specified values.
Example: Order date falls between 1 January and 31 December.
is not between
Check whether a value falls outside two specified values.
Example: Customer age is not between 18 and 25.
is longer than
Check whether a value is longer than a specified length.
Example: Product description is longer than 100 characters.
is shorter than
Check whether a value is shorter than a specified length.
Example: Category name is shorter than 50 characters.
matches regex
Check whether a value matches a regular expression.
Example: Phone number matches "^\d{3}-\d{3}-\d{4}$".
does not match regex
Check whether a value does not match a regular expression.
Example: Postal code does not match "^\d{2}-\d{3}$".
is in list
Check whether a value is in a specified list.
Example: Country code is in the list of EU codes.
is not in list
Check whether a value is not in a specified list.
Example: Product category is not in the list of withdrawn categories.
is in list (regexp)
Check whether a value matches any regular expression in a list.
Example: Email matches one of several regular-expression patterns.
is not in list (regexp)
Check whether a value matches none of the regular expressions in a list.
Example: URL matches none of the prohibited regular-expression patterns.
Conditions by data type
Text data
- is equal to
- is not equal to
- contains
- does not contain
- is blank
- is not blank
- starts with
- does not start with
- ends with
- does not end with
- is longer than
- is shorter than
- matches regex
- does not match regex
Numeric data
- is equal to
- is not equal to
- is greater than
- is greater or equal to
- is less than
- is less or equal to
- is between
- is not between
is equal to and is not equal to work with both text and numeric data. The supplied specification does not assign list operators to a specific data type.
Related articles