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:
- 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.
- Click the Quick Aggregate vertical tab. The modal window displays the values resulting from the common aggregate functions for all numeric columns.
If you are only interested in viewing aggregate values for a single column, follow these steps:
- Right click the header for the column. In the drop-down menu, select Profile….
- 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:
Right click the header for the column. In the drop-down menu, select Aggregate…. The AGGREGATE panel is displayed.The following list describes the rules and tips for entering information in a Xcalar Design panel.
- By default, the name of the column from which you start the function is displayed as an argument. If you want to use another column as an argument, you can change the argument to the name of another column.
- When typing a column name, always start with the dollar sign ($).
- When typing a column name, always include the prefix if one exists. (A derived column does not have a column name prefix.)
- If the panel calls for the resultant column name, type the name without preceding it with the dollar sign ($).
- To avoid mistakes when typing a column name as an operand, place the insertion point in the argument field, move your cursor to the active table and click anywhere in the desired column. The column's name is automatically entered in the operand field.
- If an operand accepts a column name, it also accepts the aggregate name that you created when you calculated the aggregate value for a column.
- When typing a decimal number for an operand, do not start with the decimal point. For example, type 0.5 instead of .5.
- If necessary, click in the panel to bring the specified table into focus. You can then scroll the table to make the desired column visible.
- When typing a string, do not enclose the string with double quote marks (").
When typing a string as input, escape the following characters with a backslash (\) if you want the characters to be interpreted literally:
- backslash (\)
- double quote mark (")
- dollar sign ($) if it is the first character
- caret (^) if it is the first character
- You can undo any typing with the undo key or key combination that you normally use in your browser.
- Select the aggregate function. For example, to calculate the average for the column, select the avg function.
(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.The following list describes the naming conventions:
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.
- The name is case-sensitive.
- Length must not exceed 255 characters.
- Characters must be A-Z, a-z, 0-9, hyphen (-), and underscore (_).
- First character must be a letter.
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:
- Click to display the Worksheets panel.
- In the Worksheets pane, click to display a list of tables.
- Click Aggregates to display a list of saved aggregate values. In the following example, the aggregate named minArrivalTime is displayed with its value (1).