Value transformations for attributes

Definition and types

Value transformations are robust operations that you can utilize to amend your data (modify, correct, or both) to ensure that it adheres to the requirements of your retailers. Value transformations behave as follows:

Will apply to all values for a specific attribute in a channel category if the attribute is category specific
Will apply to all values for a specific attribute in a channel (across categories) if the attribute is not a category-specific attribute
Will not transform any channel-level manual edits
Multiple transformations can be applied to an attribute (order may impact end result).

Creating transformations

Grid view should be set to create a transformation operation for an attribute in a channel category. Click the dropdown menu to open the transformation selection and then click that selection to open the transformation screen. Please note that if an attribute is constrained by a list of values (dropdown list), the only transformation that can be applied is finding a value and replacing it with a value from the predefined list.

These options can be viewed and selected from the 'Select transformations to add' dropdown.

Available pre-configured transformations

Conditional transformations

While not a mandatory action, the user can set transformations to be based on a specific value from the Master Data. This would allow a user, for instance, to decide to apply a value transformation to products with the value 'Magenta' under 'Color.' Without a condition, the value transformation will be applied, in a given category, to all products.

Date-specific transformations

Transformation	Description	Example / Notes
Add to date	A formula that allows you to use a date in your master data and then calculate a new date by adding a set number of days, months, or years.	Add one month
Change date format	Allows user to transform current date format to another format.	Transform format  YYYY-MM-DD into M/D/YYYY (2021-01-22 → 1/22/2021).
Change date to ISO	Changes a date format to ISO format.	ISO format: YYYY-MM-DD
Subtract from date	Allows you to use a date in your master data and then calculate a new date by subtracting a set number of days, months, or years.	Subtract 5 days

Generic transformations

Transformation	Description	Example / Notes
Append attribute transformation	Used to add more than one attributes from master data together to complete the requirements of the channel. Will add the value from a second attribute. You can use this transformation multiple times if you need to append more than one master data attribute. User selects separator, if needed, or can leave this blank.	Map to attribute A and then append attribute B — gives you the result of A + B together.
Append value	Will add a typed value after the attribute. Separator, if required, should be typed.	Add '.' to the end of my master data value.
Prepend value	Will add a typed value before the attribute. Separator, if required, should be typed.	Prepend value: ACME Spacing: [Blank space] ‘Cotton jumper’ → ‘ACME Cotton jumper’
Replace value	Finds and replaces a character or group of characters and substitutes with another.	Replace ‘jumper’ with ‘sweater’ ‘Cotton jumper’ → ‘Cotton sweater’
Set default value	Sets a default value if the cell is empty.	Set Currency attribute’s default value to ‘€.’ Will only apply to empty cells.
Split by	Splits string with separator.	If you need to split a string of data from an attribute in your master data such as maybe your master data contains all colors of a product as one string with / as separators (for example, blue/red/white), but the channel attribute wants the ‘/' removed, you can use this attribute to separate out the string so it shows as ‘blue red white’
Split on character count	Splits string every [X] characters.	Transforms a single string-based master data value attribute into a multi-valued string-based channel attribute. Characters per segment set to: 30 Master data value: “Preheat oven to 350 degrees F. Use butter or nonstick spray to grease a 9x13 inch cake pan. Beat 4 eggs in a large bowl or stand mixer, until thick. Beat in sugar and vanilla. In a[...] Channel value: Value#1: Preheat oven to 350 degrees F. Value#2: Use butter or nonstick spray Value#3: to grease a 9x13 inch cake pan Value#4: . Beat 4 eggs in a large bowl Value#5: or stand mixer, until thick. B Value#6: eat in sugar and vanilla. In a [...]
Truncate	Truncates the value from left to right. User selects amount of characters to keep.	If an attribute can only be 50 characters, you can use this to cut off anything that is longer than 50
Filter (inclusion)	To include entries in a composite based on matching a specified value, use double quotes around the attribute, then a colon, then double quotes around the desired value.	Example of single attribute filtering: \"ATTRIBUTE\":\"value\" Example of AND attribute filtering: (?=.\"FIRST_ATTRIBUTE\":\"first_value\")(?=.\"SECOND_ATTRIBUTE\":\"second_value\") Example of OR attribute filtering: (\"ATTRIBUTE\":\"5\")\|(\"ATTRIBUTE\":4\")"
Insert value	Adds a typed value within the attribute. User decides position within the string and selects separator, if any.	Add an x to position 10.
Lookup table	Allows user to apply a lookup value transformation on an attribute based on a lookup table uploaded under Channel Management.	Lookup all values ‘legging’ and replace with ‘jegging.’ You can have several before- and after-values in the same lookup table.

Integer-specific transformations

Transformation	Description	Example / Notes
Left pad number with zeros	Add a number of zeros to your master data value.	Commonly used to make a 12-digit GTIN into a 14-digit GTIN
Math add value	Allows you to add a value to the existing numeric part of the attribute value	Add value set to: 5 ‘45’ → ‘50’ ‘45.00’ → ‘50.00’
Math divide value	Allows you to divide the numeric part of an attribute value by a defined divisor. Set precision to tell the number of digits after period.	Master data value = 45.00 Divisor set to: 9 Precision set to: 2 Channel value = 5.00
Math multiply value	Multiplies the numeric part of an existing value by the provided value.	Multiply value set to: 2 ‘45’ → ‘90’ ‘45.00’ → ‘90.00’
Math round value	Round existing attribute value up or down to the nearest value step provided.	Rounding up to nearest 10 will transform 45 into 50
Math subtract value	Allows you to subtract a value from the existing numeric part of the attribute value.	Subtract value set to: 5 ‘45’ → ‘40’ ‘45.00’ → ‘40.00’
Set decimal places	Sets a fixed amount of decimals.	A retailer requires all numeric values to have 2 decimals. Number of decimal places set to: 2 ‘45 → 45.00’

Multivalue-specific transformations

Transformation	Description	Example / Notes
Add attribute to multivalue	If a channel field requires more than one value to be completed, you can use this attribute to add, after a comma, the value from a second attribute.	If a channel attribute requires multiple values from a list (LOV) to be provided, this is referred to as a ‘multi value’ attribute. If the different values needed for this attribute are in separate master data attributes, you can use this transformation to pull in multiple different master data attributes to populate this ‘list.’
Extract from multivalue	Allows user to decide in a multivalue which value to view in the extract.	If your master data has an attribute that has multiple values defined and you only need to use one of those values, you can use this transformation to just select the one you need.
Join multivalue with separator	Joins all values from a multivalue into one value.	If your master data attribute has multiple values but you need it to display as a string in the channel — you can use this attribute for example, you might have an ingredient list as a multi value in your master data — but the channel just wants these listed as a single string.

String-specific transformations

Transformation	Description	Example / Notes
Remove duplicated words	Removes duplicates from a string.	Leave first instance of word and remove any duplicates after that.
Remove whitespace	Removes leading and/or trailing blank spaces from a string.	‘0 12345 678905 ’ → ‘0 12345678905’ ' green sweater' → 'green sweater' ' blue shirt ' → 'blue shirt'
Transform to 'Sentence case'	Capitalizes first letter of the string.	‘cotton jumper’ → ‘Cotton jumper’
Transform to 'Title case'	Capitalizes first letter of each word.	‘cotton jumper’ → ‘Cotton Jumper’
Transform to lower case	Changes input to be lower case.	‘ACME Cotton Jumper’ → ‘acme cotton jumper’
Transform to upper case	Capitalizes all letters.	‘ACME Cotton Jumper’ → ‘ACME COTTON JUMPER’

Note: The 'Remove whitespace' transformation removes only leading and trailing whitespace.

Conditional or default transformations

If there is only one transformation implemented, that transformation will be 'default.' Additional transformations added within the same set will then be known as added 'conditions.' Conditional and default are not different types of transformations — the difference is simply in the naming convention as multiple transformations are created. And, it is not necessary to add a condition when the user wants to perform a value transformation.

Value transformation examples

The following scenarios are examples of some of the value transformations that can be performed on attributes within PDX. Please note that only mapped values can utilize value transformations.

In the first example, the values that appear in the 'Price' column are not formatted to meet the required standards of the retailer. The 'Sellable in Store' column also contains an invalid value. Value transformations can be applied to correct this error in all cells. Additionally, future product data will be corrected as it is uploaded as long as the transformations are in place.

The retailer has provided a format that requires two decimal places within a price attribute. Since this is a correction that should be made on all data in this column, no conditions are necessary. Therefore, a transformation that adds the decimal values to 'Price' data should be used.

The user will scroll through the 'Select transformation to add' dropdown to locate the correct transformation and then add the transformation.

In this instance, the user should add two decimal places. The 'Sample values' section will show how the data will be changed. If the change is correct, click .

The data will now reflect the corrected value, showing the old, incorrect value in parentheses.

More than one transformation can be used for the same attribute. In addition to incorrectly formatted values, 'Price' also has missing values. We can add a second transformation to automatically fill in the empty values.

Another way to transform values is through the lookup table method. The lookup table allows users to change multiple instances of a value for an attribute. In the following example, a lookup table will be used to change values in the description attribute field.

The user wants to replace all instances of 'This is a scented soap' that appear in the Description attribute with 'This is a fragrant soap bar.' Below are the steps to make this value transformation using a lookup table.

Create an excel spreadsheet that contains the old value and new value.
1. Create two columns to contain the old and new values. In the example below, the columns have simply been named 'Old' and 'New,' but there is no naming rule for the columns. Although the example shown contains only one value transformation, multiple old and new value sets can be identified for value transformation in the column.
2. Name the tab containing the values an identifiable name.
3. Save the spreadsheet the name of choice. The example below has been named 'Lookup table.'
Open the 'Channels management' screen and click on the channel with values needing transformation.
Click 'Lookup table' tab and then use to upload excel file. Once uploaded, the file will appear in the 'Lookup table' tab.
Once the table has been loaded, return to the Channel overview and open the Value transformations for the attribute to modify.
Select 'Lookup table' as the value transformation to add.
Select value named as the tab was named and click .
The attribute data will now reflect the new data.