Mastering data transformation

Akeneo supports formulas typically found in most desktop spreadsheet packages. Functions can be used to create formulas that manipulate data and calculate strings and numbers ensuring your product information meets the specific requirements of any channel.

Here's a list of all the functions available. 

Information & limits

here are a few key details about Akeneo functions:

• Language: Akeneo functions are currently available in English only.
• Capacity: For each target attribute, you can use up to 10 functions and create up to 5 levels of nesting. This gives you significant flexibility for complex transformations.

Understanding Multi-Locale Targets

When your target attribute handles localized values, Akeneo applies transformations on a per-locale basis. This ensures that your translated content is transformed accurately for each language you manage.

Localized data example

Suppose in Akeneo PIM, you have:

• A sku (stock keeping unit) with no channel or locale specific values.
• A color attribute (simple select) with no channel or locale specific values.
• A description attribute with values per channel and per locale.

For your e-commerce channel, you want to combine these three attributes to populate your website's product description in several languages. You configure a transformation like this: CONCATENATE(sku, "-", color, ": ", description)

Here's how Akeneo PIM handles this transformation for different locales:

• For the English locale: Akeneo PIM will send the concatenation of your product SKU, the English label of the color attribute, and the product name in English.
• For other managed locales: Akeneo PIM will perform the same concatenation, using the appropriate translated color label and product name for each respective locale.

This ensures your product descriptions are perfectly localized and transformed for every language you support.

List of available functions

Here's a detailed breakdown of the functions you can use in Akeneo PIM, complete with sample usages and syntax.

CONCATENATE

Appends strings together.

Sample usage:

CONCATENATE("Welcome", " ", "to", " ", "Akeneo!")

Result: "Welcome to Akeneo!"

Syntax:

CONCATENATE(string1, [string2, ...])

• string1: The initial string.
• string2 … [OPTIONAL] Additional strings to append in sequence.

Notes:

• You can concatenate up to 10 strings.

MERGE

Transforms a list of values into a single string separated by commas.

Sample usage:

MERGE(["red", "blue", "yellow"])

Result: “red, blue, yellow"

Sample usage:

If you have a source attribute like categories which returns an array of values:

MERGE(categories)

Result: “LED TVs, Computer monitors, Samsung”

Syntax:

MERGE(array)

• array:the array of strings to merge.

FIRST

Returns the first item in a table.

Sample usage

FIRST(["red", "blue", "yellow"])

Result: “red”

Sample usage

FIRST(categories)

Result:“LED TVs”

Syntax

FIRST(array)

• array - The initial array of strings.

SPLIT

Divides text around a specified character or string and puts each fragment into a separate string in a table of strings.

Sample usage

SPLIT("1,2,3", ",")

Result: result is a table of strings: ["1", "2", "3"]

Sample usage

SPLIT("Akeneo Product Cloud", " ")

Result: result is a table of strings: ["Akeneo", "Product", "Cloud"]

Sample usage

SPLIT("Lorem-ipsum dolor et", "-")

Result: result is a table of strings: ["Lorem", "ipsum dolor et"]

More advanced examples with optional parameters:

Sample usage

SPLIT("printer scanner 3in1 3kg 5306114", "31", TRUE, TRUE) or SPLIT("printer scanner 3in1 3kg 5306114", "31")

Result: result is a table of strings: ["printer scanner", "in", " ", "kg 5", "06", "4"]

Sample usage

SPLIT("printer scanner 3in1 3kg 5306114", "31", TRUE, FALSE)

Result: result is a table of strings: ["printer scanner", "in", " ", "kg 5", "06", "", "4"]

Sample usage

SPLIT("printer scanner 3in1 3kg 5306114", "31", FALSE, FALSE)

Result: result is a table of one string: ["printer scanner 3in1 3kg 5306114"]

Syntax

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

• text - The text to divide.
• delimiter - The character or characters to use to split text.
  • By default, each character in delimiter is considered individually, e.g. if delimiter is "31", then text is divided around the characters "3" and "1". Set split_by_each to FALSE to turn off this behavior.
• split_by_each - [ OPTIONAL - TRUE by default ] - Whether or not to divide text around each character contained in delimiter.
• remove_empty_text - [ OPTIONAL - TRUE by default ] - Whether or not to remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty source values are added between consecutive delimiters.

Notes

• The character(s) used for splitting will not be included in the resulting array.

UPPER

Converts a specified string to uppercase.

Sample usage

UPPER("lorem ipsum") 

Result: “LOREM IPSUM”

Syntax

UPPER(text)

• text - The string to convert to uppercase.

LOWER

Converts a specified string to lowercase.

Sample usage

LOWER("LOREM IPSUM") 

Result: "lorem ipsum"

Syntax

LOWER(text)

• text - The string to convert to lowercase.

PROPER

Capitalizes each word in a specified string.

Sample usage

PROPER("united states") 

Result: United States

Sample usage

PROPER("uNITed statES") 

Result: United States

Sample usage

PROPER("unitedstates") 

Result Unitedstates

Syntax

PROPER(text)

• text - The text which will be returned with the first letter of each word in uppercase and all other letters in lowercase.

Notes

• PROPER is useful for proper nouns, such as names of people or geographic locations.
• PROPER capitalizes each word in text rather than the beginning of each sentence, and is, therefore, likely not the correct tool to use for paragraphs or other blocks of text.
• PROPER will convert all characters not at the beginning of words to lowercase, which may cause problems with certain strings. For example, using PROPER("mcLeod") to capitalize the surname McLeod results in "Mcleod" instead.

LEFT

Returns a substring from the beginning of a specified string.

Sample Usage

LEFT("Akeneo", 2)

Result: Ak

Sample Usage

LEFT("lorem ipsum")

Result: l

Syntax

LEFT(string, [number_of_characters])

• string - The string from which the left portion will be returned.
• number_of_characters - [ OPTIONAL - 1 by default ] - The number of characters to return from the left side of string.

Notes

• 0 is a valid input for number_of_characters and will cause LEFT to return the empty string.

TRIM

Removes leading, trailing, and repeated spaces in text.

Sample Usage

TRIM(" Akeneo")

Result: “Akeneo”

TRIM("lorem ipsum ")

Result: “lorem ipsum”

Syntax

TRIM(text)

• text - The string or reference to a source containing a string to be trimmed.

Notes

• TRIM removes all spaces in a text string, leaving just a single space between words.
• Whitespace or non-breaking space will not be trimmed.

REGEXREPLACE

Replaces part of a text string with a different text string using regular expressions.

Sample usage

REGEXREPLACE("Akeneo 101", "[0-9]+","777") 

Result: "Akeneo 777"

REGEXREPLACE("The price is $826.25","[0-9]*\.[0-9]+[0-9]+", "315.75") 

Result: “The price is $315.75”

REGEXREPLACE("(Content) between brackets", "\(([A-Za-z]+)\)", "Word") 

 Result: “Word between brackets"

Syntax

REGEXREPLACE(text, regular_expression, replacement)

• text - The text, a part of which will be replaced.
• regular_expression - The regular expression. All matching instances in text will be replaced.
• replacement - The text which will be inserted into the original text.

Notes

• We support PCRE2 (PHP >= 7.3). Learn more on how to use PCRE2 expressions. You can also use the regex101 website to test your regular expression. 
• This function only works with text (not numbers) as input and returns text as output.

REGEXEXTRACT

Extracts the first matching substrings according to a regular expression.

Sample usage

REGEXEXTRACT("My favorite number is 241, but my friend's is 17", "\d+")

 Result: "241"

Syntax

REGEXEXTRACT(text, regular_expression)

• text - The input text.
• regular_expression - The first part of text that matches this expression will be returned.

Notes

• We support PCRE2 (PHP >= 7.3). Learn more on how to use PCRE2 expressions. You can also use the regex101 website to test your regular expression. 
• This function only works with text (not numbers) as input and returns text as output.

CLEANHTML

Cleans all the HTML tags automatically in your textarea attributes used as a source.

Sample usage

CLEANHTML("My product description with <span style="font-weight: bold;">bold</span> and <span style="font-style: italic;">italic</span> text.")

Result: "My product description with bold and italic text."

Syntax

CLEANHTML(text)

• text - The input text to clean.

ISEMPTY

Checks whether the attribute, property or value is empty.

Sample usage

ISEMPTY("value")

Result: FALSE

Sample usage

ISEMPTY("")

Result: TRUE

ISEMPTY(null)

Result: TRUE

Syntax

ISEMPTY(value)

• value - Reference to the attribute or property that will be checked for emptiness. valuecan be a string or a table of strings.
  • When value is a string, ISEMPTY returns TRUE if value is empty or null, and FALSE if it contains characters.
  • When value is a table of strings, ISEMPTY returns TRUE if the table is empty or null, and FALSE if it contains at least a string with characters.

Notes

• This function is most often used in conjunction with IF in conditional statements.

IFS

Evaluates multiple conditions and return a value corresponding to the first true condition.

Sample usage

IFS(title = "", "Default title", title = null, "Default title", title != "" AND title != null, title)

Result:

• if the source title is empty or null: “Default title”
•  if the source title has a value: "The title value"

Syntax

IFS(condition1, value1, [condition2, value2, …])

• condition1 - The first condition to be evaluated. 
• value1 - The returned value if condition1 is TRUE.
• condition2, value2, … - Additional conditions and values if the first one is evaluated to be false.

Notes

• You can add up to 5 condition/value combinations. 
• If all conditions are FALSE, an error is returned. 

ROUND

Rounds numbers.

Sample usage

ROUND(826.645, 0) 

Result: 827

Sample usage

ROUND(826.645) 

Result: 827

Sample usage

ROUND(826.645, 1) 

Result: 826.6 (decimal number place 1)

Sample usage

ROUND(826.645, 2) 

Result: 826.65 (decimal number place 2)

Sample usage

ROUND(826.645, -1) 

Result: 830 (decimal number place -1)

Sample usage

ROUND(826.645, -2) 

Result: 800  (decimal number place -2)

Syntax

ROUND(value, [places])

• value - The value to round to places number of places.
• places - [ OPTIONAL - 0 by default ] - The number of decimal places to which to round.

Notes

• Places may be negative, in which case value is rounded at the specified number of digits to the left of the decimal point.
• Standard rules indicate that the next most significant digit (the digit to the right) is considered when rounding to a particular place. It is rounded up if this digit is greater than or equal to 5. Otherwise, it is rounded down. 

TOTEXT

Converts content to string.

Sample usage

TOTEXT(StyleCode)

Style code value in the PIM was 345 Result: (345)

Syntax

TOTEXT(number|boolean)

• number - The first condition to be evaluated. 
• boolean - The returned value

VALUE

Returns a float from a string or an integer. 

Sample usage

VALUE(string|number)

Result (3)

Syntax

VALUE(string|number)

• string - The first condition to be evaluated. 
• number - The returned value if condition1 is TRUE.

INT

Returns an integer from a string or a float.

Sample usage

INT(BR_code|number)

Result (3.00)

Syntax

INT(string|number)

• string - The first condition to be evaluated. 
• numbers - The returned number

Notes

• This is the same function as Value, which only handles integers.

MEASUREMENT

Creates a measurement object from value and unit.

Sample usage

MEASUREMENT(length, "CM")

Result: 38 CM

Syntax

MEASUREMENT(value, unit)

• value - The value to use as a measurement.
• unit - The unit the measurement should be used as.

Notes

• Do not forget to add “” around the unit.
• to concatenate multiple measurements for a string target, you can use the functions CONCATENATE and TOTEXT
  • Ex: in the PIM there are 3 attributes “length”(cm), “height”(cm), “width”(cm) that are measurements, the destination has an attribute “Product Dimensions” with the requirements: 35 cm x 12 cm x 54 cm. To map to this requirement correctly, you can use the following syntax: [CONCATENATE(TOTEXT(length)," cm"," x ",TOTEXT(width)," cm"," x ",TOTEXT(height)," cm")]

ARRAYELEMENT

Extracts an element from an array with its index.

Sample usage

ARRAYELEMENT(Image_URL, 2)

>> Asset #2 from the asset collection position 

Result: https://www.francetvinfo.fr/pictures/ZZdUwxSIZmCaeBI_cxPGpl68Dps/1200x1200/2013/06/19/041_MAN364AU.jpg

Syntax

ARRAYELEMENT(array, index)

• array - The asset collection from which the selection takes place.
• index - The position from which the array needs to fetch. It can be an integer for a multi-select, for example, or a string for selecting a table attribute line.

Notes

• The index, when it's numeric, starts at 1.

ARRAYSORT

Sorts a two-dimensional array (e.g., a table attribute) based on the values in a specific column of the second-level array.

Sample usage

ARRAYSORT(composition, “per_100g”, “desc”)

Syntax

ARRAYSORT(array, columnCode[, direction])

• array - The asset collection from which the selection takes place.
• columnCode - The identifier for the column that will be used for sorting.
• direction - Use 'asc' (default value) for ascending order and 'desc' for descending order.

Notes

• The new array is numerically indexed and the index starts at 1.

ENTITYRECORDATTRIBUTEVALUE

Extracts the value of a specified attribute from a record in a reference entity.

Sample usage

ENTITYRECORDATTRIBUTEVALUE("brandb, “muuto”, “country”)

>> Reference entity brand with a record with code “muuto”, with a text attribute with code “country”.

Result: “Denmark”

Syntax

ENTITYRECORDATTRIBUTEVALUE(referenceEntityCode, recordCode, attributeCode[, channel[, locale]])

• referenceEntityCode - The reference entity to select.
• recordCode - The concerned record from the reference entity.
• attributeCode - The attribute from which extract the value.
• channel - For attribute with value per channel, defines the desired channel. If the attribute has a value per locale but not per channel, you must enter null.
• locale - For attribute with value per locale, defines the concerned locale.

TABLESELECTOPTIONLABEL

Extracts the translated label of a specified option from a column of type select belonging to a table attribute.

Sample usage

TABLESELECTOPTIONLABEL("components", "material", "iron", "fr_FR")

 Result: “Fer”

Syntax

TABLESELECTOPTIONLABEL(tableAttributeCode, colomnCode, optionCode, locale)

• tableAttributeCode - The code of the concerned table attribute.
• colomnCode - The code of the column of type select belonging to the table attribute.
• optionCode - The option code from which extract the value.
• locale - The locale in which translate the option label.