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:
- Right click the header of the column in which you want to count the occurrences of unique values.
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.
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.
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.
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.
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.
Follow these steps to filter or exclude a value through the Profile window:
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.
- 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:
- Hover over the bar graph or pie chart in the Profile window to display the Download icon.
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.