Sorting a column and finding quartiles

You can sort values in a column so that values are displayed in ascending or descending order. Rows containing FNF in the sorted column are not included in the resultant table. Therefore, it is possible that the resultant table contains fewer rows than before the sort. For more information about FNF, see Working with a table containing FNF.

IMPORTANT: Sorting only affects how the rows are displayed. Subsequent operations do not preserve the result of the sort. For example, you can sort on a column named Age to display rows ordered by age. Later if you perform an arithmetic function to sum two other columns, the resultant table displayed after the function is in the original, unsorted order. The rows are no longer sorted by age.

After you sort a column, you can determine the quartiles for the values in the column.

Sorting values in a column

Follow these steps to sort a column:

  1. In the column drop-down menu, select Sort. A pop-up is displayed for you to specify whether to sort in ascending or descending order.
  2. If the data type of the column is String and Xcalar Design decides that most of the values in the column are numbers, it displays the Sort Suggestion dialog box. Depending on the desired method of sorting, click Alphabetically or Numerically.

    NOTE: If you select Numerically, the data type of the column is changed to integer.

The column header includes an arrow indicating the column is sorted. The following screenshot shows a part of the Distance_integer column that is sorted.

Sorting values in multiple columns

NOTE: Columns of String type in a multi-column sort are always sorted alphabetically.

Follow these steps to sort multiple columns:

  1. To sort a particular column first, display the column options from that column. Then select Sort to display the pop-up.
  2. Click Advanced … in the pop-up to display the Sort panel. The column name is displayed in the Sort panel.
  3. Select another column (by clicking anywhere in the column) as the second column to be sorted. Repeat this step for each additional column that you want to sort.

    To deselect a column, click the column again.

    The column names are listed in the Sort panel in the same order as you select the columns. Also, each column header is associated with a number (for example, 1, 2, and so on), indicating the order in which the columns will be sorted. In the following screenshot, the DayOfWeek column is selected first, followed by the AirTime column.

  4. In the Sort panel, for each selected column, specify whether the sort is in ascending or descending order.
  5. Click SORT.

How quartile values are affected by sorting a string column

If a column being sorted contains FNF (field not found), the FNF rows are removed from the resultant table. (For more information about FNF, see Sorting a table by a column containing FNF.) If sorting a string column gives you a choice of sorting numerically or alphabetically, your choice affects how many rows will contain FNF after the sort. As a result, you might get different quartile values from the same column, depending on whether the column is sorted numerically or alphabetically.

Example: In a table with 10 rows, suppose a string column called Age contains NA in 4 rows and numbers in other rows. If you sort values in this column alphabetically, the resultant table contains 10 rows just as it did before sorting. If you sort numerically, because the rows containing NA cannot be converted to integer, they now contain FNF and are not included in the resultant table. The table after sorting contains 6 rows. After you generate the quartile values, you can see that the values from the 10-row table are different from those from the 6-row table.

Finding quartiles in a column

After you sort a column, follow these steps to determine the quartiles for the values in the column:

  1. Right click the column header to display the column drop-down menu.
  2. Click Profile… in the drop-down menu. The Profile modal window containing the quartile summary section is displayed. Click Generate for the quartile summary. The following screenshot shows an example of the quartile summary.

Go to top