Calculated Field Sets

Owned by Maroš Grman
Last updated: Oct 27, 2023 by Pricefx Learning Hub
4 min read

From the diagram below, notice how the data travels from the ERP system through the Integration Manager into the Pricefx application. Our Product data contains the average cost but only in euros. Using the Calculated Field Set (CFS) we can use the average cost in euros and calculate the average cost in dollars and then enrich the product data with this new calculated value.

Figure 1. Data flow to Pricefx with Calculated Field Set enrichment

Business Use Case

  • The Price Managers require that the Product Cost table contains the average cost (of producing the product) in a secondary currency, US dollar.

It can lead to the following system use case:

  • The average cost comes from an ERP system via the Integration Manager (IM), but only in a single currency, euro. Therefore, the average cost in the secondary currency needs to be calculated each time new product data is uploaded via the Integration Manager.

Concept

The main part of the CFS Concept is to understand how a Logic is applied on rows of the data table. The Logic is applied on each row which is fetched using the api. We can have multiple elements which transform the data of the current row. After the transformation, the Logic then outputs the transformed data back in to the data table.

In the example below we can see how this is done on the Master Data Table.

Data Transformation

Data transformation in the principle of manipulating or modifying data either in place or in the midst of some manner of data migration.

Transformation can take place in these areas:

  • ERP middleware

  • Integration Manager

  • Within Pricefx environment

This handbook will be focusing on data transformations in the Pricefx environment. The most typical areas within the Pricefx environment, where data transformations are done are:

  • Master Tables (Product, Customer, etc.)

  • Data Sources

  • Datamarts

What is a Calculated Field Set

Calculated Field Set is a data enrichment/transformation/manipulation task, which is usually performed during integration (after upload of data) to enrich/modify/transform/clean values of columns in Master Data tables (e.g., Products, Customers, Extensions, Company Parameters, …​).

It processes the filtered rows of the source dataset, performs a calculation and then writes the results back into the target column.

CFS uses data processing logic to calculate new values of attributes (e.g., reference prices, values based on comparison and other product/customer parameters) based on other existing data.

The calculation mechanism used for these data enrichment sets is the same as for the price calculation.

A Calculated Field Set will operate on all rows in a particular source or a subset of those rows generated using a filter.

As we can see in the diagram above, the CFS can use many different tables as a source. Most of the time it’s the master data tables: Product, Customer, Company Parameter, Product Extensions and Customer Extensions.

The source tables can also come from Price Records, Rebate Records and Manual Price Lists.

CFS Source Tables

In this context the source tables mean data which is used as an input for the CFS. Most of the time it’s the master data tables: Product, Customer, Company Parameter, Product Extensions and Customer Extensions.

The source tables can also come from Price Records, Rebate Records and Manual Price Lists.

Implementation / Configuration

Logics

To better understand the concept, let’s look at a real example of data transformation using a logic on Product Extensions Table.

Steps:

  1. The logic is applied on a row in the Product Extensions Table. The api.currentItem() is used to load the row into the logic and we take the attribute1, which is the Average Cost column.

  2. In the return statement we take the Average cost and multiply it by the exchange rate.

  3. The transformed value is returned and Mapped to the Average Cost USD row.

Each of these steps is applied on every row of the data table.

Configuration

  1. In the partition navigate to Administration › Calculated Field Sets.

  2. Create a new CFS.

  3. Fill out the Label and Target date.

  4. Select your CFS.

  5. In the source selection tab, pick the Source type, e.g., Product Extension.

  6. In the Calculation Setup tab, pick your logic and configure the Field mapping:

    1. Element Name is the name of the Element in our logic which returns the transformed value.

    2. Output Field is the column where the value will be mapped.

The most important parts of this configuration are the selection of logic and correct mapping from element to the output field.

After this configuration you can run the CFS by clicking on the Calculate button.

This configuration then needs to be fetched in to the project. The reason for this is that all the settings must also be in Git, otherwise it will not get to QA and PROD.

Summary

As we can see the Calculated Field Set is a powerful tool for data transformation, manipulation or enrichment. Typical use of a CFS would be transformation of data coming from some ERP system via the Integration Manager. Thanks to this, we can transform the data to match our business use case.

References

Documentation

Found an issue in documentation? Write to us.