Aggregate Data

tell me more icon Tell me more.

Use an aggregate node to transform the data in SALES so that it is summarized (aggregated) on a per-customer basis.

Follow these steps to create the aggregation:

  1. In the Component Palette, click the Transforms section, and left-click Aggregate. Move the cursor to the workflow and click again. Name the node AggregateSALES.

  2. Right-click SALES, select Connect, and connect SALES to AggregateSALES.

  3. Double-click AggregateSALES. The Edit Aggregate Node dialog opens.

  4. At the top of the dialog, click Edit to select a Group By column. Edit Group By launches. Check that Type is Column. Move CUST_ID from Available Attributes to Selected Attributes. Click OK.

  5. Back in the Edit Aggregate Node dialog, click launch aggregation wizard to launch the Define Aggregation wizard.

  6. Follow these steps to define the aggregation:

    1. First select the aggregation functions. Select the SUM() function in the list of Numerical functions; click Next.
    2. Next select the columns to apply the function to. Move QUANTITY_SOLD and AMOUNT_SOLD from Available Attributes to Selected Attributes; click Next.
    3. Next select a Sub Group By column. Make sure that Type is Column. Move PROD_ID from Available Attributes to Selected Attributes; click Next.
      This selection results in a nested table. For each customer, the new table contains total quantity sold and total amount sold on a per product basis.
    4. The wizard provides default names for the new columns; click Finish to accept the default names and complete the specification of the aggregation.

    tell me more icon Tell me more about what I see in the GUI

  7. Back in the Edit Aggregation dialog, click OK.

  8. Right-click AggregateSALES and select Run to run the aggregation.

The cue cards icon Next step is to join the aggregated data to the customer data.