Determining aggregate values

Determining an aggregate value means using values from rows in a column as input to calculate a new value. For example, in a table showing employee salaries, you can run an aggregate function on the Salary column to determine the average salary.

You can determine the aggregate values for numeric columns in one of two ways:

  • Calculate aggregate values for multiple selected columns in a table.
  • Calculate aggregate values for a selected column.

This section explains how to determine aggregate values from numeric values. One aggregate function, listAgg, is a string function. For more information about listAgg, see Understanding the listAgg (list aggregate) function.

Calculating aggregate values for multiple columns

Follow these steps to calculate aggregate values for all numeric columns in a table:

  1. Select Correlations… in the table drop-down menu or in the column drop-down menu, depending on whether you want to see aggregate values for selected columns or all columns in the table. The Correlation and Quick Aggregates modal window is displayed. For more information about displaying the Correlation and Quick Aggregates modal window, see Determining correlation between two values.
  2. Click the Quick Aggregate vertical tab. The modal window displays the values resulting from the common aggregate functions for all numeric columns.

Calculating aggregate values for a selected column

If you are only interested in viewing aggregate values for a single column, follow these steps:

  1. Right click the header for the column. In the drop-down menu, select Profile….
  2. In the Profile modal window, if the Aggregate Summary section does not display the aggregate values, click Generate, which displays the values resulting from common aggregate functions for the column.

If you are interested in calculating one particular aggregate value in a selected column or if you plan to use the aggregate value as input for other functions, follow these steps:

  1. Right click the header for the column. In the drop-down menu, select Aggregate…. The AGGREGATE panel is displayed.

  2. Select the aggregate function. For example, to calculate the average for the column, select the avg function.
  3. (Optional) In the New Resultant Aggregate Name field, type the name of the aggregate value. The caret (^) is automatically entered in the field because the name must be preceded by the caret.

    IMPORTANT: If you plan to use an aggregate value in the future as input to a function such as a Map function, Xcalar strongly recommends that you assign a name to the value. In this way, you can use the name as an argument to the function. Do not use the actual aggregate value as the argument because the value might not be accurate when you run the function against the entire data source or when you use the function against another dataset.
    EXAMPLE: You can name the resultant aggregate value average_salary (which shows as ^average_salary in the AGGREGATE panel). Naming a resultant value is useful if you plan to use the value as input for another function. In this example, you can type average_salary as an operand in a MAP or FILTER panel, or you can type ^average_salary as input for a function in the function bar.

Displaying saved aggregate values

If you use the New Resultant Aggregate Name field in the AGGREGATE panel, the value is saved under the aggregate name. If you want to use one of the saved aggregate values as input to a function, you can choose the value from a list of saved aggregate values.

Follow these steps to display the saved aggregate values:

  1. Click to display the Worksheets panel.
  2. In the Worksheets pane, click to display a list of tables.
  3. Click Aggregates to display a list of saved aggregate values. In the following example, the aggregate named minArrivalTime is displayed with its value (1).

 

Go to top