Liquid Studio Documentation
Data Mapping / Data Mapper Functions / By Category / Aggregate / Sum
In This Topic
    Sum
    In This Topic

    Function Name
    Sum
    Category
    Aggregate
    Icon
    Sum
    Description
    Retrieve the sum of values within a context
    Inputs
    Context Context within which to determine sum of values [optional]
    Value Value to retrieve sum of
    Outputs
    Result Sum of values within context
    Properties
    None -

    Usage

    The Data Mapper Aggregate Sum function allows you to retrieve the sum of a set of values within a specific context. The Context input is optional, so you can simply retrieve the sum of the specified values for the whole data source by not connecting it. If you do connect the Context input, the sum is taken within the specified parent node context. To apply the Aggregate Sum function, use the following process:

    Create a new Data Mapper file, dragging your XML data source and targets into the editor area. For this example we are using sellers_source.xsd, an XML Schema Definition inferred from the following source:

    Source XML
    Copy Code
    <sellers>
     <salesperson>
      <name>John Davidson</name>
      <sales>12800</sales>
     </salesperson>
     <salesperson>
      <name>Jane Johnson</name>
      <sales>23050</sales>
     </salesperson>
     <salesperson>
      <name>Mark Smith</name>
      <sales>18200</sales>
     </salesperson>
    </sellers>
    

    For our target schema we will be using sellers_target.xsd, inferred from the following XML:

    Target XML
    Copy Code
    <sales_staff>
     <sales_data type="sum">85220</sales_data>
     <sales_assistant seller_name="Ken Jameson">
      <amount>34020</amount>
     </sales_assistant>
     <sales_assistant seller_name="Lisa Matthews">
      <amount>27500</amount>
     </sales_assistant>
     <sales_assistant seller_name="Paul Norton">
      <amount>23700</amount>
     </sales_assistant>
    </sales_staff>
    

    The data models sales for individual salespeople within a retail organisation. Let's assume that, as well as mapping the salespeople and their data, we also want to determine the sum of sales across all staff members. We will record this in the "sales_data" element together with a String indicating the type of data we are recording as in the target above. Here is the Data Mapper with Source and Target imported:

    Data Mapper Source and Target

    Drag the Aggregate Sum function from the Component Palette into the mapping area:

    Aggregate Sum Component

    Sum Added

    The Sum function takes two inputs, representing the value to retrieve the sum of and the context in which it should be calculated. Both inputs and output of the Sum function may be connected to items in the XML Reader/ Writer or other components in the Mapper.

    Connect the Context input of the Sum function to the "sellers" output in the XML Reader and the Value input to the "sales" output.

    Reader to Sum

    This will output the sum of sales across all sellers. In this case, the effect would be the same if we did not connect the Context input, however if we had categories of sellers, for example in different stores, we would perhaps want to calculate the sum of sales for each store, in which case the Context input would be different. Connect the Sum output to the Element Value input within the "sales_data" element in the XML Writer.

    Sum to Writer

    This will ensure that the "sales_data" element is written out with the sum of sales inside it each time the data is mapped.

    Now we need to define the type of data we are including in the "sales_data" element. Drag a Constant Value from the Data Type section onto the Mapper. Right-click it and choose Show Properties. Choose a String Data Type enter "sum" as the Value, or anything else you prefer.

    Constant Properties

    Connect the Constant output to the "type" input in the XML Writer.

    Constant to Writer

    Finally let's make the remaining input and output connections. Remember to map "sellers" to "sales_staff" and "sales" to "amount" - although we have used these as references we have not yet mapped them. We do not need to map anything to the "sales_data" element input as the Element Value will ensure the element itself is mapped.

    Inputs and Outputs Connected

    We can now execute the transform by pressing Shift-F5 or the Execute button (Execute). The transform is applied and the file we selected as output opens in the editor:

    Output XML
    Copy Code
    <sales_staff>
     <sales_data type="sum">54050</sales_data>
     <sales_assistant seller_name="John Davidson">
      <amount>12800</amount>
     </sales_assistant>
     <sales_assistant seller_name="Jane Johnson">
      <amount>23050</amount>
     </sales_assistant>
     <sales_assistant seller_name="Mark Smith">
      <amount>18200</amount>
     </sales_assistant>
    </sales_staff>
    

    The output contains the data values mapped from the source and the sum of sales included in the "sales_data" element, together with a String indicating the meaning of the value.

    If you do not connect the Context input of the Sum function, the result will be the sum for the data source as a whole.
    Due to type promotion, the output of the Sum function may be a value of a different type to the input values.
    See Also