Automate

Incorporating values from an Excel spreadsheet as criteria and for data transformations can enhance the effectiveness of your Studio Query scripts.

 

In this Tip of the Week, we are going to use an example of updating customer material pricing conditions.  As sample Query script and Excel file are included with this tip.

 

Use case:  You sell a selection of material numbers to a particular customer. You want to retrieve data associated with those materials within a designated sales organization and distribution channel. Additionally, you'd like to automatically calculate a specific price adjustment and set new validity dates for when those changes should take effect. You aim to accomplish all of this using Studio and Excel.

 

Let's break this down into two things:

1. Criteria - Did you know you can use Excel data as criteria in your Query scripts? By setting criteria, you can filter for only the information you need, and users can enter these criteria directly in Excel instead of through the Studio popup window. If you're unfamiliar with how to use Excel values as criteria, refer to the Criteria section below for more details.
2. Data Transformation - When your Query script includes data transformation, you can create Excel formulas that automatically populate the relevant columns in your spreadsheet. For more information, refer to the Data Transformation section below.

 

 

Criteria

 

In this example, you can use Excel data for four different criteria:

 

1. Customer Number (equal to)
2. Sales Organization (equal to)
3. Distribution Channel (equal to)
4. Material Number (between)

 

In the attached example, the criteria like this:

 

 

 

Steps for creating criteria using Excel are below. 

Note:  Criteria are created after Excel data mapping is completed.

 

Equal to criterion example:

 

1. Go to the criteria tab in the Studio Query script
2. Choose the operator from the drop down menu (see 1 below)
3. Choose Cell - The source for this value is a cell from an Excel file (see 2 below)
4. Enter the Excel cell to use for this criteria (see 3 below)
5. Repeat for each criterion.

 

 

 

Between between criterion example (for the material number range):

 

 

 

See attached Studio Query template for the two examples above and other examples.

 

 

 

Data Transformation

 

By using Transform Rules (Excel formulas) within Query scripts to transform data, your spreadsheet is automatically populated-eliminating the need to manually enter formulas in each row. Additionally, because the formula resides in the script rather than in Excel itself, there's less risk of it being accidentally deleted or modified.

 

Tips: 

• Create and test your formula in Excel before adding it to your Studio script. 
• Important:  Transform rules are automatically incremented for each row.  The formula in the Transform Rule should always be the Start Row of mapped data.  Example:

 

• The starting row of output is row 9:

 

 

 

 

 

The formula shown multiplies the fixed value in cell J2 (2%) by the Current Amount in column G to calculate the New Amount in column H. When configuring Transform Rules for columns, be sure to define the Start Row in both the Transform Rule and the target cell. The Query script will then automatically update the row number for each entry.

 

 

 

• This example is using a Query script.  However, data transformations will work in Transaction scripts as well.
• Sometimes, you may need to create a Transform Rule (Excel formula) that doesn't directly relate to any of the data you're downloading. In these situations, you can select any field from the table you're querying, enter your Transform Rule, and map that row accordingly.
• In Studio scripts, you can customize the Field description column on the mapping screen as needed. For rows containing Transform Rules, consider updating the Field description to clearly explain what each rule does. In the example below, the Field description has been modified to provide a human-readable summary of the Transform Rule's function.

 

 

 

 

In this example, there are five different data transformations:

 

1. New Valid Start Date - SAP requires the dates to be uploaded as text, so this formula reformats the date provided as text
2. New Valid End Date - SAP requires the dates to be uploaded as text, so this formula reformats the date provided as text
3. Percentage change - calculates the Percent Change based on the percentage provided
4. New Amount - calculates the new price (Current Amount * percentage provided)
5. Condition Pricing unit - the value for the condition pricing unit is extracted from SAP with decimal places.  In this example, the unit has to be a whole number to be uploaded to SAP. 

 

Data Transformation example:

1. There are two ways to transform data.
1. Data that needs to be downloaded AND transformed.  In this example, you can download the Condition pricing unit into one column and apply the transformation in a separate column using a formula. For instance, the original value of 1.00 could be placed in column AA (or another column out of view), while the transformed value of 1 appears in column K9. The original data mapping and Transform Rule are shown below.
1. Note:  A random field from the table being queried was selected to write this formula.

 

Original (AA)

 

 

 

Transform Rule - Transformation formula and mapping to a second column (K9)

 

 

Results

 

Example of results of the Criteria and Transform Rules:

 

 

  

See attached Studio Query script and Excel template to see examples.

------------------------------
Tammy Lake
Sales Engineer
*Precisely Software Inc.
------------------------------