Foundation Documentation and SupportUser The Prep ToolWhat Preps are available in the Configuration Tool? (Examples of each Prep)

# What Preps are available in the Configuration Tool? (Examples of each Prep)

1. Count

Let's look at the Count Prep (preparation).

We will add two of the same Fields (Browser), and then apply the Count Prep to one of them.

Notice that both of the Browser columns have identical information in them (before the Count Prep gets applied). Click the Prep icon. Select the Count Prep. Click the Run icon to apply the changes.  Notice that the Count Prep has been applied to Browser(1). 2. Count Distinct

Let's look at the Count Distinct Prep (preparation).

We added three of the exact same Fields (and typed a number next to each one to keep them organized).  We clicked the Run icon to show that all three of the Location columns have identical information in them (before we apply any Preps to the data).

Click the Prep icon.  Select Count Distinct. Click the Run icon to apply the changes.  Notice the data in Location(3) looks totally different from the data in Location(1) and Location(2), now that the Prep is applied.

For a contrast, we will click the Prep icon on Location(2).  Select Count. Click the Run icon.  Notice the Count Prep has large numbers in Location(2), but the Count Distinct has only 1's in Location(3).

3. Distinct

Let's explore the Distinct Prep (Preparation).

For this example we have two identical Location Fields (we typed a 1 and a 2 next to them, to keep them organized). Notice that there are 6,474 records of data (before we apply a Prep to the data). Click the Prep icon for Location 2.  Select Distinct from the menu. Click the Run icon to apply the changes.  Notice the Distinct Prep reduced all 6,474 records into only 3 Distinct records.  4. Find Minimum

Let's look at the Find Minimum Prep (preparation).

For this example we have a Location Field and an Amount Field.  Clicking the Run icon displays the data under each column. Notice there are 6,474 records (before we apply a Prep). Click the Prep icon (for the Amount Field), then select Find Minimum. Click the Run icon to apply the changes:

• 52 is the minimum Amount from Gilbert's Gallery Location.
• 51 is the minimum Amount from Bob Actions Location.
• 50 is the minimum Amount from Fort Fun Location. 5. Find Maximum

Let's look at the Find Maximum Prep (preparation).

This example shows a Location Field and an Amount Field.  Clicking the Run icon displays the data in each column. Notice that there are 6,474 records (before we apply a Prep). Click the Prep icon in the Amount Field.  Select Find Maximum. Click the Run icon to apply the changes:

• 1500 is the maximum Amount in all of the Gilbert's Gallery Location data.
• 1500 is the maximum Amount in all of the Bob Actions Location data.
• 1499 is the maximum Amount in all of the Fort Fun Location data. 6. Average

Let's look at the Average Prep (preparation).

For this example, we can see the Location data and the Amount data in the columns on the right. Notice that there are 6,474 records (before we apply a Prep). Click the Prep icon next to the Amount Field.  Select Average from the menu. Click the Run icon to apply the changes.  The average Amount is now listed for each Location. 7. Sum

Let's look at the Sum Prep (preparation).

For this example, we can see the Location names and the various Amounts in the columns on the right side. Notice there are 6,474 records (before we apply a Prep). Click the Prep icon next to the Amount Field.  Select Sum from the menu. Click the Run icon to apply the selected changes.  Notice the sum of the Amount for each Location. 8. Delta Change

A Delta Change is the change in value from the previous value.  It needs to be a single data set by another data set

For our example, there is existing data in the image:

• We have a Date Field.
• We have two Visits Fields (and we've typed "(example)" beside the second one, to help keep things organized). Notice how the Delta Change shows the value change:

• There were 2 Visits on the first Date listed (compared to none listed before it), so it shows 2 as the Delta Change in the Visits (example).
• There were 4 Visits on the second Date listed (compared to the 2 Visits the Date before), so it shows 2 as the Delta Change in the Visits (example).
• There were 5 Visits on the third Date listed (compared to the 4 Visits the Date before), so it shows 1 as the Delta Change in the Visits (example)
9. Round

Let's look at the Round Prep (preparation).

For this example, we have one Location Field, and four Amount Fields (each Amount Field has a description typed next to it - to help keep it organized with what we will demonstrate).  All of the data in each Amount column looks identical so far (since we have not applied a Prep to them yet).

We applied the Average Prep to all of the Amount Fields, and then applied an additional Round Prep to three of the Amount Fields.

After applying the Preps, we clicked the Run icon (and can see the results):

Let's explore the results from applying the Preps to the Amount Fields:

• Average (average) - our example shows numbers (with four digits beyond the decimal point).
• Average (round to floor) - our example rounded DOWN (turning 755.5340 to be 750 for the Fort Fun Location).
• Average (round) - our example rounded 125.0000 to be 130 for the Greenhouse Location.
• Average (round to ceiling) - our example rounded UP (turning 766.9104 to be 770 for the Bob Actions Location). 10. If Empty

Let's look at the If Empty Prep (preparation).

For this example, we can see that there is data in several of the columns.  But the URL Field is "NULL" (not showing any data).

Notice the "N/A" displayed in the URL (if empty) column, instead of the "Blank" or "NULL." 11. Find and Replace

Let's look at the Find and Replace Prep (preparation).

For this example, we will demonstrate two Browser Fields (one with the Prep applied to it, and the other without it). 12. Replace (Exact)

Let's look at the Replace (Exact List) Prep (preparation).

For this example, we will demonstrate two Browser Fields (one with the Prep applied to it, and the other without it). 13. Change Case

Let's look at the Change Case Prep (preparation).

We can see the results of the Change Case Prep applied to the Location Fields:

• Location(original) - has no Prep applied to it.
• Location(uppercase) - the Prep changes all the data to uppercase.
• Location(lowercase) - the Prep changes all the data to lowercase.

This image is looking at the result of the Change Case Prep for the Locations:

• Location(original) - has NO Preps applied to it.
• Location(uppercase) has the Change Case - Uppercase Prep applied.
• Location(lowercase) has the Change Case - Lowercase Prep applied. 14. Trim

The Trim Prep will remove the "white space" off of data: Trailing, Leading, or Both:

1. Leading Trim - will remove "white space" that is BEFORE the data.

2. Trailing Trim - will remove "white space" that is AFTER the data.

3. Trim Both - will remove "white spaces" BEFORE and AFTER the data.

Notice that all of the Random Notes columns look identical (with the way the numbers and spacing are displayed).  It does NOT look like the Trim Prep seems to be working - but it actually IS working correctly.  We have to look at the actual data being displayed: 1a. Right-click on the row of numbers below Random Notes, to open the right-click menu.  Select Show Data from the menu. 1b. Notice the extra spaces BEFORE and AFTER the data. 2a. Right-click on the row of numbers below Random Notes(trailing), to open the right-click menu.  Select Show Data from the menu. 2b. Notice the extra spaces BEFORE and AFTER the data of the Random Notes(trailing).  The Trailing section has been Trimmed. 3a. Right-click on the row of numbers below Random Notes(leading), to open the right-click menu.  Select Show Data from the menu. 3b. Notice the extra spaces BEFORE and AFTER the data of the Random Notes(leading).  The Leading section has been Trimmed. 4a. Right-click on the row of numbers below Random Notes(both), to open the right-click menu.  Select Show Data from the menu. 4b. Notice the extra spaces BEFORE and AFTER the data of the Random Notes(both).  Both sections have been Trimmed. 15. Arithmetic

Let's look at the Arithmetic Prep (preparation).

For this example, we will use the Amount Field:

1. We applied the Currency Format.  (Here is a link to the "Format" examples in the Prep Tool)

2. We applied the Arithmetic Prep.

1. We selected Division from the Operation menu.

2. We typed "100" in the By.

3. We clicked the Run icon to apply the changes. Observe the side-by-side results of the Arithmetic Prep.

16. Clean

Let's look at the Clean Prep (preparation).

We will demonstrate how to Clean some content in our Random Notes column.  We will Clean "..." and ".." from the Random Notes content.

17. Substring

The Substring Prep looks at the positions of the data.

The Substring Prep will return:  a piece of the data (specified by a starting point, and a number length to go beyond that starting point).

Notice that all of the Fruit each meal columns have identical data showing (BEFORE we apply the Substring Prep to the Breakfast, Lunch, and Dinner columns).

The Grapes data under Fruit per meal is showing " 7,5,9 "

• Position 1 = the 7
• Position 2 = the " , "
• Position 3 = the 5
• Position 4 = the " , "
• Position 5 = the 9
18. Split

Let's look at the Split Prep (preparation).

For this example, we will use six variations of the Random Notes Field.

The first Random Notes: this Field will NOT have any Prep applied to it.

Random Notes (1)-(5):  these Fields will have a Split Prep (applied slightly differently) to each of them.

The first Random Notes Field:

This Field does NOT have any Preps applied to it.  It shows data separated by commas.

Random Notes (1)-(5)

The Split Prep was set for each (with a "," typed in, and a number typed in (1,2,3,4, or 5) to specify the position number).

The system looked for the content in the position number that was entered.  19. Concatenate

Let's look at the Concatenate Prep (preparation).  We will use three of the same Fields (Fruit Name) with the same content:

• We can demonstrate the Concatenate Append Prep by typing in this content: " = Append example "
• We can demonstrate the Concatenate Prepend Prep by typing this content: " Prepend example = "
20. Pivot

Let's look at the Pivot Prep (preparation).

For this example, we have a Date Field, a Visits Field, and a Location Field.  Each Field has data displaying in the columns. If we add an additional Location Field and click the Run icon, we would expect to see a fourth column on the Table.  But, lets see what actually happens when we apply the Pivot Prep to that additional Location Field.

Notice:

• The Pivot Prep did NOT display one more Location column on the Table.  Instead, this example of the Pivot Prep added 3 columns to our Table!
• It made the display of the data do a Pivot (showing the names of each Location listed at the top, and then organizing each column by the Location name).
21. List

Let's look at the List Prep (preparation).

We will use a Location, Date, and Visits Field - specifically watching the Visits on only one Date.  The result should give us numbers of Visits (for Breakfast, Lunch, and Dinner) at three Eatery Locations, during one Date. We applied the List Prep to the Visits, so the system grouped the Visits based on the Date and specific Location. 22. Distinct List

The Distinct List Prep will return a distinct list of data for that column.

To set up this example, we will use the Location Field.  Having a second Location Field, and applying the Count Prep to it, we can see what the Locations are (and how many there are for each of the Locations).  The result we want from this example is to have one row of information under the Location column with all of the Locations listed on that row of information. We added a third Location Field, and applied the Distinct List Prep to it.  Then we turned OFF the visibility of the other two Location Fields.

We clicked the Run icon to apply the changes, and now we can see one row with the Distinct List of Locations being displayed.

23. Range

The Range gives us the Maximum minus the Minimum value using the GROUP BY.  If you have 3 rows of a number and date, it can calculate the Range.

For this example, the Date stays the same, but the Good Fruit data has three numbers: (98, 77, 96).  We will add a second Good Fruit Field to apply the Range Prep to it.  You can turn OFF/ON the visibility of the specific Field(s) to understand how the system calculates the Range Prep. The Range Prep did the math of the Maximum value minus the Minimum value (98-77=21), for the Range of the Good Fruit Field on the specified Date. 24. Absolute Value

The Absolute Value Prep will show the Positive Value of the data.

For this example, there are two Fields named Bad Fruit.  We addded a description of "(absolute value)" to one of them to help keep it organized.

Here is a link to another article that can be a useful combination with Preps:  The available Formats.