Counting occurrences of unique values in a column

To count the occurrences (frequency) of each value in a column, use the Profile column option, which displays a window with statistical information.

Displaying the Profile window

Follow these steps to count the occurrences (frequency) of each value in a column:

  1. Right click the header of the column in which you want to count the occurrences of unique values.
  2. From the drop-down menu, select Profileā€¦. The Profile window containing a bar graph is displayed. The bar graph shows the distribution of values in the column.

TIP: It might take a while for the bar graph to display. You can close the window and re-select Profileā€¦ in the column drop-down menu later to view the finished bar graph.
NOTE: Using Profile to determine the frequency of each value does not create a table. If you need to create a table to show the frequency of each value, use the Count function, which is one of the Group By functions. For more information about Group By functions, see Grouping data in a table.

Types of charts in the Profile window

You can use a toggle switch in the Profile window to display either a bar graph or a pie chart. The following screenshot shows the location of the switch.

Sorting the graph

The following screenshot illustrates how you can sort the bars in a bar graph. In this examples, the values in the DayOfWeek column are sorted by name in ascending order. You can sort the slices in a pie chart in the same way as illustrated in this screenshot.

Displaying frequency with range bucketing

If the data type of a column is Integer or Float, you can use the Range bucketing drop-down list to determine how the bars or slices are displayed, as described in the following list:

  • To count occurrences of single values, select Single. Each bar or slice represents the frequency of one value.
  • To count the occurrences of the values that fall in a range, select Range and enter the range size. Each range is represented by a bar or a slice. Enter a numeric value in the Range field and then press Enter. For example, 0.5 and 20 are valid values.
  • To instruct Xcalar Design to choose the range size so that all bars or slices can fit in the window, select Fit All.
NOTE: A bar or slice is displayed only if there is at least one occurrence of the value represented by the bar. For example, if the range size is 50, and there are no occurrences of any value between 0 and 50, the graph does not display a bar or slice for this range.

Specifying decimal places for values

If the data type of a column is Integer or Float, you can specify the number of decimal places for each displayed value in the bar graph or pie chart.

For example, if you specify 0 decimal places, the values are displayed as 1, 2, and so on. If you specify 2 decimal places, the values are displayed as 1.00, 2.00, and so on.

Specifying the number of results displayed

If you select Fit All in the Range bucketing drop-down list, you cannot control the number of results displayed. Otherwise, you can increase or decrease the number of results displayed. The number is incremented by 10.

The following screenshot illustrates how you control the number of results in a bar graph.

The following screenshot illustrates the pie chart displayed with the same settings as in the bar graph. The slice named Other is the category of results that would otherwise be too small to display individually.

NOTE: The bar showing the frequency of the null value is displayed in the bar graph or pie chart of each page. It is not counted in the number of results shown. The number of results reflects only the non-null values.

Displaying counts or percentages

By default, the frequency is displayed as a count. Click a bar or slice to change the count to a percentage.

In a bar graph, if the number or percentage is not completely visible because it is wider than the bar, hover over the bar to display a tooltip, which contains the field name, the value (either as a single value or as a range), and the frequency.

In a pie chart, hover over a slice to display the field name, the value (either as a single value or as a range), and the frequency.

EXAMPLE: Suppose the value 1 in the selected column appears 3,000 times, the count (3,000) is displayed on the bar. Click anywhere in the bar and the count changes to 20%, which means that 20% of the values in the column are 1.

Filtering by using the Profile window

To filter or exclude a value in a column, follow the instructions in Filtering in a table column. In addition, you can perform filtering after viewing the frequencies of various values in a column. The Profile window provides a convenient way to filter or exclude a value in the table. You can also filter or exclude a range of values.

NOTE: From a pie chart, you cannot filter the values in the Other category.

Follow these steps to filter or exclude a value through the Profile window:

  1. Place the cursor in the bar or slice for the value to be filtered or excluded. Drag in any direction inside the bar or slice. To select multiple bars or slices, repeat this step for each bar or slice.

    TIP: if the bars or slices are contiguous, move the cursor across them.

    After you finish selecting, a pop-up displays the options for you to filter or exclude the values.

  2. Select Filter this value in the pop-up to include only this value (or a range of values). Select Exclude to exclude this value (or a range of values). The Profile window is closed after you make the selection, and the column in the table is updated.

The following screenshot provides an example of how to filter or exclude two values by using the bar graph.

Downloading the Profile window to a PNG file

To download the Profile window to a file named profile.png on your computer, follow these steps:

  1. Hover over the bar graph or pie chart in the Profile window to display the Download icon.
  2. After the Download icon is displayed, as shown in the following screenshot, click the icon to start the download.

Temporary tables resulting from the Profile window

When you display the Profile window, temporary tables are created. These tables are for Xcalar Design to generate profile results more efficiently from the same column at a later time.

Go to top