Syntax | Frequency Analysis | Vantage Analytics Library - Syntax - Vantage Analytics Library

CALL td_analyze (
  'frequency',
  'required_parameter_list [ optional_parameter; [...] ]'
);

required_parameter_list

database = input_database_name;
tablename = input_table_name;
columns = { column_name [,...] | keyword };

optional_parameter

{ columnstoexclude = column_name [,...] |
  cumulativeoption = { true | false } |
  gensqlonly = { true | false } |
  having = expression |
  minimumpercentage = min_percentage [,...] |
  outputdatabase = output_database_name |
  outputtablename = output_table_name |
  overwrite = { true | false } |
  pairwisecolumns = column_name [,...] |
  statisticscolumns = column_name [,...] |
  topvalues = top_values |
  style = { basic | pairwise | crosstab } |
  where = expression
}

Syntax Elements

database

The database containing the table to analyze.

tablename

The table containing the columns to analyze.

columns

The column or columns to analyze—a single BYTE column, one or more columns of any other data type, or a keyword.

If you specify a single BYTE column, the function returns a result set. It cannot return an output table.

If you specify multiple columns:

• You cannot specify style=crosstab.
• The function builds a VOLATILE table, processing all columns in a single CREATE VOLATILE TABLE AS SELECT statement and reformatting data with individual INSERT/SELECT statements into the final output dataset.

keyword	Description
all	All columns.
allnumeric	All numeric columns.
allcharacter	All character columns.

columnstoexclude

[Optional] The columns to exclude when columns specifies a keyword.

cumulativeoption

[Optional] Whether to include rank, cumulative count, and cumulative percent information for each frequency value.

Disallowed with style=pairwise.

Default: false

gensqlonly

[Optional] True returns the SQL for the function as a result set but does not run it.

False runs the SQL for the function but does not return it as a result set.

Default: false

having

[Optional] A SQL HAVING clause to generate within the function SQL to restrict returned aggregations. Example:

HAVING xpct > 1

Disallowed with minimumpercentage.

minimumpercentage

[Optional] The minimum percentage of the time that frequency values must occur to be included in the analysis.

minimumpercentage (0) or minimumpercentage (0.0) is not equivalent to omitting minimumpercentage.

Default: No minimum percentage

outputdatabase

[Optional] The name of the database to contain the analysis results table.

Disallowed if columns specifies a single BYTE column.

outputtablename

[Optional] The name of the table to store the analysis results.

Disallowed if columns specifies a single BYTE column.

Default behavior: Function returns results as a result set but does not create an output table.

overwrite

[Optional] Whether to drop the output tables before creating new ones.

Default: true

pairwisecolumns

[Optional] The columns to pair with the frequency columns.

Allowed only with style=pairwise.

statisticscolumns

[Optional] The columns for which to include in the result set or output table the minimum, maximum, mean value, and standard deviation for itself with the values computed over the rows corresponding to the individual values of the columns specified by columns.

Disallowed with cumulativeoption=true, style=pairwise, and style=crosstab.

style

[Optional] This option sets the frequency style to one of three values.

Style	Description
basic (default)	Counts frequencies of individual column values. If columns specifies multiple columns: Function repeats the analysis on each column. You cannot specify a view with tablename. If you specify outputdatabase and outputtablename, generated SQL has one CREATE TABLE statement followed by multiple INSERT/SELECT statements.
pairwise	Counts frequencies of pairwise combinations of values of selected columns rather than individually. Disallowed with cumulativeoption=true.
crosstab	Counts frequencies of combinations of values of selected columns rather than individually. Generated SQL has single SELECT statement for all columns specified by columns. Disallowed if columns specifies multiple columns.

topvalues

[Optional] The maximum top occurring frequency values to include in the result set or output table. For example, for topvalues=10, the result set or output table includes only the top 10 frequency values.

Allowed only with cumulativeoption=true.

where

[Optional] The expression in the SQL WHERE clause to include in the generated SQL to filter rows selected for analysis.

Examples:

• where = cust_id > 0 includes this WHERE clause in the generated SQL:

  WHERE cust_id > 0

• where = gender = ''F'' includes this WHERE clause in the generated SQL:

  WHERE gender='F'