| Excerpt |
|---|
| supports computation of counts of rows, columns, and ad-hoc values within your data, so that you can make assessments of the quality, consistency, and statistical validity of your data. |
Important Note on Counts
| Warning |
|---|
Any computed counts that you see in the Transformer page are computed from the displayed sample. |
These computed counts reflect the entire dataset, only if the data grid is displaying the full dataset:
| D caption |
|---|
|
Data grid sample is the full dataset. |
When the job is executed, however, any computations of counts are applied across the entire dataset.
Visual Profiling
When you run a job, you can enable the profiling of the job results, which renders a visual profile and some statistics on the dataset. This profile is available for review through the application. For more information, see Overview of Visual Profiling.
Row and Column Counts
In the status bar at the bottom of the data grid, you can review the current count of rows and columns in the displayed sample.
| Tip |
|---|
Tip: The row and column counts in the status bar may be useful for comparing the changes to these metrics between steps. For example, you can click step 2 in your recipe and then review these metrics. When you click step 3, these metrics may change. |
Row counts: Depending on your method of sampling, the row counts may change. For more information, see Overview of Sampling.
Column counts: By default, all columns in the panel are displayed. Column counts should change only if you delete or hide them.
Computed row counts
You can use the following functions to identify and compute the row counts in your dataset.
| Function Name | Description |
|---|
| COUNT Function | | D excerpt include |
|---|
| page | COUNT Function |
|---|
| nopanel | true |
|---|
|
| Tip |
|---|
Tip Typically, this function is used as part of an aggregation, in which rows are grouped according to shared values in other columns. This function can also be applied without grouping, which is called a flat aggregate. More information on how to apply aggregated counts is below. |
|
| ROWNUMBER Function | | D excerpt include |
|---|
| page | ROWNUMBER Function |
|---|
| nopanel | true |
|---|
|
|
| SOURCEROWNUMBER Function | | D excerpt include |
|---|
| page | SOURCEROWNUMBER Function |
|---|
| nopanel | true |
|---|
|
| Info |
|---|
NOTE: This function may fail to return results if the original source row information is not available. For example, if you have performed a join between multiple datasets, the source row number information cannot be computed. Similarly, if you compute this function and then perform a join, the results may not make sense. |
| Tip |
|---|
Tip: You can pair this function later with the MIN or MAX functions to compute the highest and lowest row number information. |
|
Count by Pattern
These transformations allow you to compute counts of literals or patterns in a cell's values. Then, you can perform calculations on this new column of values to compute metrics across the dataset.
Count pattern or text
The following example computes the number of references in the tweet column for My Company:
| D trans |
|---|
| p03Value | tweetCompanyReferences |
|---|
| Type | step |
|---|
| p01Name | Option |
|---|
| p01Value | Text or pattern |
|---|
| p02Name | Text or pattern to count |
|---|
| p02Value | 'My Company' |
|---|
| p03Name | New column name |
|---|
| SearchTerm | Count matches |
|---|
|
Suppose, however, that the company has multiple ways in which it is reference. It could be:
My CompanyMy CoMy Company, Inc.
You can modify the above transformation to use a
to capture these variations:
| D trans |
|---|
| p03Value | tweetCompanyReferences |
|---|
| Type | step |
|---|
| p01Name | Option |
|---|
| p01Value | Text or pattern |
|---|
| p02Name | Text or pattern to count |
|---|
| p02Value | `(My Company|My Co|My Company, Inc.)` |
|---|
| p03Name | New column name |
|---|
| SearchTerm | Count matches |
|---|
|
If needed, you can use the following to add up all of the counts in tweetCompanyReferences to determine the total number.
| Info |
|---|
NOTE: Keep in mind that this sum reflects only the sum of values in the sample in the data grid. When you run a job containing this calculation, it is applied across all rows in the dataset. |
| D trans |
|---|
| p03Value | sum_tweetCompanyReferences |
|---|
| Type | step |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | SUM(tweetCompanyReferences) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Count between patterns
You can also collect counts of values between two patterns within a cell's value. In this manner, you can analyze a more constrained substring of the cell value.
The following transformation calculates the URLs in each row of the msgText column, assuming that the URL begins with http:// or https:// and ends with .com or .net:
| D trans |
|---|
| p03Value | Selected |
|---|
| p06Name | Ignore case |
|---|
| p01Name | Option |
|---|
| p06Value | Selected |
|---|
| p03Name | Include as part of the match |
|---|
| p07Value | countURLs |
|---|
| p04Value | `(\.com|\.net)` |
|---|
| SearchTerm | Count matches |
|---|
| p07Name | New column name |
|---|
| Type | step |
|---|
| p05Name | Includes as part of the match |
|---|
| p01Value | Between two delimiters |
|---|
| p02Name | Starting pattern |
|---|
| p02Value | `(http\:\/\/|https\:\/\/)` |
|---|
| p05Value | Selected |
|---|
| p04Name | Ending pattern |
|---|
|
Count Functions
Aggregated counts
You can perform calculations based on groups that you define as part of the calculation. These groupings, called aggregations, are powerful tools for delivering insightful analysis on your data.
In the following example, several aggregated computations, including the COUNT function are performed on transactional data, which is grouped by region (regionId) and product (prodId):
| D trans |
|---|
| p03Value | SUM(sales) |
|---|
| Type | step |
|---|
| p05Name | Type |
|---|
| p01Name | Group by 1 |
|---|
| p01Value | regionId |
|---|
| p02Name | Group by 2 |
|---|
| p02Value | prodId |
|---|
| p05Value | Group by as new column(s) |
|---|
| p03Name | Values 1 |
|---|
| p04Value | COUNT() |
|---|
| p04Name | Values 2 |
|---|
| SearchTerm | Group by |
|---|
|
| Info |
|---|
NOTE: The above calculation inserts two new columns into the dataset. Alternatively, you can choose to do a full replacement of the dataset with these aggregated counts. For more information, see Pivot Data. |
Conditional count functions
You can use a set of functions that count occurrences, based on conditions. In the following list of functions:
- Some of the conditions are implicit in the function itself. For example,
COUNTA counts values that are non-null. - Some conditions are specified as part of the function. For example,
COUNTIF tabulates counts provided a specified condition is met.
The following transformation counts the rows where the length of msgText is longer than 140 characters, grouped by userId:
| D trans |
|---|
| p03Value | Group by as new column(s) |
|---|
| Type | step |
|---|
| p01Name | Group by 1 |
|---|
| p01Value | userId |
|---|
| p02Name | Values 1 |
|---|
| p02Value | COUNTIF(LEN(msgText)>140) |
|---|
| p03Name | Type |
|---|
| SearchTerm | Group by |
|---|
|
| D s also |
|---|
| inCQL | true |
|---|
| label | ((label = "discovery_tasks") OR (label = "transformation_ui") OR (label = "count")) |
|---|
|
