Value Transformations for Attributes

Definition and Types

Value transformations are 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 of an attribute to show the Value transformations option. Click Value transformations to open the transformation screen for that attribute. 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.

Note: Only attributes that have been mapped will contain the option for Value transformations.

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 the user, for instance, to decide to apply a value transformation to products with the value 'Carrot' under 'FLAVOR.' Without a condition, the value transformation will be applied, in a given category, to all products. Multiple conditions can be set under a single transformation.

Steps

1. Click 'Add condition.'

2. Select master data attribute from dropdown. The dropdown will contain values for the attributes available in the channel.

3. Select condition. Available conditions in the dropdown are listed below:

   1. Equals

   2. Contains

   3. Doesn’t contain

   4. Doesn’t equal

   5. Greater than

   6. Is empty

   7. Isn’t empty

   8. Less than

4. Insert value to which condition should be applied (if relevant).

Transformation Overview

The General transformations section contains the process to select the type of transformation to perform. Multiple transformation actions can be added to one transformation set.

Transformation types available in the 'Select transformation to add' dropdown are described in the tables below.

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.
Behavior for not found values	Will add a typed value after the attribute. Separator, if required, should be typed.	Add '.' to the end of my master data value.
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\")"
Ignore retrieved values	Any retrieved values will be ignored when calculating the attribute’s value. This transformation must be applied as the last in the chain of transformations, i.e., last in the transformations overview and in the general transformations list.
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.
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’
Regular expression	A sequence of characters that defines a search pattern. A common example of data transformation using regular expressions involves cleaning and standardizing data.	Replace (123) 456-7890 123.456.7890 123-456-7890 1234567890 with 123-456-7890 in all instances.
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.
Append newline	Adds a newline character to the end of the current text.	This will typically be used in combination with other value transformations that bring several texts in play, e.g., via Append or Prepend. In the example below, two values are first combined, and then a newline is inserted after the first Value using the Append Newline Value transformation Value1Value2 → Value1 Value2
Replace value with newline	Replaces a configured text string, e.g., ‘<br>’, with the newline character.	1In the example below, <br> is replaced by a newline: 124 Main Street<br>Apt 4B<br>Springfield<br>IL 62704<br>USA → 124 Main Street Apt 4B Springfield IL 62704 USA

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. General transformations for Replace value transformations now include 'Apply to whole word,' 'Replace all occurrences,' and 'Ignore case.'

Value Transformation Examples

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

Value Transformation Without Condition

In the below 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.

Value Transformation with Condition

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.

Value Transformation with Lookup Table

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.

1. 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.'

      

2. Open the 'Channels management' screen and click on the channel with values needing transformation.

   

3. Click 'Lookup table' tab and then use to upload excel file. Once uploaded, the file will appear in the 'Lookup table' tab.

   

   

4. Once the table has been loaded, return to the Channel overview and open the Value transformations for the attribute to modify.

   

5. Select 'Lookup table' as the value transformation to add.

   

6. Select value named as the tab was named and click .

   

7. The attribute data will now reflect the new data.