Syntax | Matrix Building Function | Vantage Analytics Library - Syntax

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

required_parameter_list

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

optional_parameter

{ columnstoexclude = column_name [,...] |
  groupby = column_name [,...] |
  matrixoutput = { COLUMNS | VARBYTE } |
  matrixtype = { COR | COV | SSCP | CSSCP | ESSCP } |
  nullhanding = { IGNORE | ZERO } |
  outputdatabase = output_database_name |
  outputtablename = output_table_name |
  overwrite = { true | false } |
  where = expression
}

Syntax Elements

database

The database containing the input table.

tablename

The input table from which to build a matrix.

columns

The columns to analyze.

keyword	Description
all	All columns.
allnumeric	All numeric columns.

columnstoexclude

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

Any groupby columns are automatically excluded.

groupby

[Optional] The input table columns for which to separately analyze each value or combination of values.

Do not use the following names for groupby columns. These names are reserved for use by the CALCMATRIX table operator.

c
rowname
rownum
s

The function builds a separate matrix for each combination of values, storing the matrixes in the same output table or result dataset.

When building a matrix containing more than one group by column where the group by columns have different character sets, the columns with the Latin characters should be listed before columns with Unicode.

For example, if the column gender is Unicode and marital_status is Latin, the groupby parameter should be groupby marital_status, gender.

Default behavior: Input is not grouped.

matrixoutput

[Optional] Whether to return matrix output as COLUMNS in a table or as VARBYTE values, one to a column, in a reduced output table.

Default: COLUMNS

matrixtype

[Optional] The type of matrix to build.

Matrix Type	Description
COR	Pearson-product moment correlation matrix. Function calculates correlation for each pairwise combination of columns X and Y with this formula, where n is total number of rows in calculation:
COV	Covariance matrix. Function calculates covariance for each pairwise combination of columns X and Y with this formula, where n is total number of rows in calculation:
SSCP	Sum-of-squares-and-cross-products matrix. Function calculates sum-of-squares-and-cross-products for each pairwise combination of columns X and Y with this formula, where n is total number of rows in calculation:
CSSCP	Corrected-sum-of-squares-and-cross-products matrix. Function calculates corrected-sum-of-squares-and-cross-products for each pairwise combination of columns X and Y with this formula, where n is total number of rows in calculation:
ESSCP (default)	Extended-sum-of-squares-and-cross-products matrix. Function calculates extended-sum-of-squares-and-cross-products for each pairwise combination of columns X and Y. Formula is the same as for corrected-sum-of-squares-and-cross-products with addition of extra column with constant 1.

nullhandling

[Optional] How to handle NULL values in calculations—ignore rows that contain NULL values or replace each NULL value with zero.

Default: IGNORE

outputdatabase

[Optional] The database that contains the resulting matrix output table.

If you do not specify both outputdatabase and outputtablename, the function returns a result set instead of an output table.

outputtablename

[Optional] The name of the output table representing one or more matrixes. If you specify groupby columns, the output table has a matrix for each combination of groupby column values.

If you do not specify both outputdatabase and outputtablename, the function returns a result set instead of an output table.

overwrite

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

Default: 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'
```

Syntax | Matrix Building Function | Vantage Analytics Library - Syntax - Vantage Analytics Library

Vantage Analytics Library User Guide

Syntax Elements