Syntax | Association Rules | Vantage Analytics Library - Syntax - Vantage Analytics Library

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

required_parameter_list

database = input_database_name;
tablename = input_table_name;
groupcolumn = column_name;
itemcolumn = column_name;
outputdatabase = output_database_name;

optional_parameter

{ combinations = combination [,...] |
  description_table_options |
  dropsupporttables = { true | false } |
  gensqlonly = { true | false } |
  groupcount = group_count |
  hierarchy_table_options |
  left-side_lookup_table_options |
  minimumconfidence = min_confidence |
  minimumlift = min_lift |
  minimumsupport = min_support |
  minimumzscore = min_zscore |
  orderingprobability = ordering_prob |
  outputtablename = output_table_name [,...] |
  overwrite = { true | false } |
  processtype = { all | support | recalculate } |
  reduced_input_options |
  relaxedordering = { true | false } |
  resulttableprefix = 'prefix' |
  right-side_lookup_table_options |
  sequencecolumn = column_name |
  where = expression
}

description_table_options

descriptiondatabase = description_database_name;
descriptiontable = description_table_name;
descriptionidentifier = description_table_join_column;
descriptioncolumn = description_table_names_column;

hierarchy_table_options

hierarchydatabase = hierarchy_database_name;
hierarchytable = hierarchy_table_name;
hierarchyitemcolumn = low_hierarchy_table_column;
hierarchycolumn = high_hierarchy_table_column;

left-side_lookup_table_options

leftlookupdatabase = leftlookup_database_name;
leftlookuptable = leftlookup_table_name;
leftlookupcolumn = leftlookup_table_column;

reduced_input_options

reducedinputdatabase = reduced_input_database_name;
reducedinputtable = reduced_input_table_name;

right-side_lookup_table_options

rightlookupdatabase = rightlookup_database_name;
rightlookuptable = rightlookup_table_name;
rightlookupcolumn = rightlookup_table_column;

Syntax Elements

database

The database containing the table to analyze.

tablename

The table containing the columns to analyze.

groupcolumn

The column representing groups in the association rules.

itemcolumn

The column representing items in the association rules.

combinations

[Optional] Number items on the left and right sides of requested association rules.

The syntax of a combination is the number of items on the left immediately followed by the number of items on the right (with no space between them). For example, 11 (one item on the left and one item on the right) produces an analysis of 1-to-1, and 21 produces an analysis of 2-to-1.

The sum of the left and right numbers must be less than or equal to 5.

Default: 11 (1-to-1 analysis)

description_table_options

[Optional] A set of options to join with results sets (but not output tables) to make the results easier to understand. Specify either all or none of these options.

descriptiondatabase

The database containing the description table.

descriptiontable

The description table to join with the output data.

descriptionidentifier

The column in the description table that is joined with the result table.

descriptioncolumn

The column in the description table that contains descriptive item names.

dropsupporttables

[Optional] Whether to drop intermediate support tables after processing.

Default: true

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

groupcount

[Optional] The count (number) of groups in the input data.

This parameter is useful when you are processing a reduced input set saved in a previous run, so calculations can be based on the number of groups in the original input set rather than the reduced set. See reduced_input_options.

Default behavior: The generated SQL calculates this value.

hierarchy_table_options

[Optional] A set of options to join with the input table to reduce the amount of input data and compute association rules at a different hierarchical level. Specify either all or none of these options.

These options perform listwise deletion, ignoring rows that contain a null group, item, or sequence column value.

hierarchydatabase

The database containing the hierarchy table.

hierarchytable

The hierarchy table to join with the input data.

hierarchyitemcolumn

The lowest-level item column in the hierarchy table to be matched with the item column in the input data.

hierarchycolumn

The higher-level item in the hierarchy table. The items in the input table must match the lowest level in the hierarchy table.

Example: Three-level hierarchy table compatible with Association analysis if the input table matches the column ITEM1.

ITEM1	ITEM2	ITEM3	DESC1	DESC2	DESC3
A	P	Y	Savings	Passbook	Deposit
B	P	Y	Checking	Passbook	Deposit
C	W	Z	Atm	Electronic	Access
D	S	X	Charge	Short	Credit
E	T	Y	CD	Term	Deposit
F	T	Y	IRA	Term	Deposit
G	L	X	Mortgage	Long	Credit
H	L	X	Equity	Long	Credit
I	S	X	Auto	Short	Credit
J	W	Z	Internet	Electronic	Access

left-side_lookup_table_options

[Optional] A set of options to limit the reported rules to those with left-side items that appear in the lookup table. Specify either all or none of these options.

leftlookupdatabase

The database containing the left-side lookup table.

leftlookuptable

The left-side lookup table.

leftlookupcolumn

The column to match with left-side items in rules.

minimumconfidence

[Optional] The minimum value that the confidence measure of an association rule must have to be included in a result table. The value must be numeric and in the range [0, 1].

minimumlift

[Optional] The minimum value that the lift measure of an association rule must have to be included in a result table. The value must be numeric and nonnegative.

minimumsupport

[Optional] The minimum value that the support measure of an association rule must have to be included in a result table. The value must be numeric and in the range [0, 1].

Specifying this value reduces the size of the input data, which might affect the impact of the reduced_input_options.

minimumzscore

[Optional] The minimum value that the Z Score measure of an association rule must have to be included in a result table. The value must be numeric.

orderingprobability

[Optional] The probability of correct ordering, a numeric value in the range (0, 1]. To ignore the probability of correct ordering in Lift and Z-score calculations, set this value to 1.

Default behavior: The function determines ordering probabilities.

outputdatabase

The name of the database that contains the analysis results tables.

outputtablename

[Optional] The names of the tables to store the analysis results.

If you specify multiple combinations, specify an output_table_name for each combination. The nth combination corresponds to the nth output_table_name.

Default: The resulttableprefix parameter determines this value.

overwrite

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

Default: true

processtype

[Optional] The process type, which is one of the following:

Process Type	Description
all (default)	Builds support tables and calculates final affinities.
support	Builds support tables only. Use this option to view support tables and determine minimal support value, reducing amount of processing. Single item support table _TWM_1_ITEM_SUPPORT is created in output_database_name, regardless of value of output_table_name. If you specify resulttableprefix, support table name is prefix_1_ITEM_SUPPORT.
recalculate	Calculates final affinities using existing support tables, built from earlier function call with dropsupporttables (false).

reduced_input_options

[Optional] A set of options to use with the hierarchy_table_options, minimumsupport, or a WHERE clause to save the resulting reduced input table for further analysis. Specify either all or none of these options.

reducedinputdatabase

The database containing the reduced input table.

reducedinputtable

The table in which to store reduced input rows. See groupcount.

These parameters do not affect the reduced_input_options:

• minimumconfidence
• minimumlift
• minimumzscore
• left-side_lookup_table_options
• right-side_lookup_table_options

relaxedordering

[Optional] Whether, in the sequencecolumn, the items on the left side of an association rule may occur in any order, followed by the items on the right side of an association rule.

Default: false

resulttableprefix

[Optional] The prefix to use in the names of intermediate support tables and default output table names.

Default output table names include the calculation combination. For example, _TWM_1_TO_1_AFFINITY is the default output file name for a 1-to-1 combination.

Default prefix: '_TWM'

right-side_lookup_table_options

[Optional] A set of options to limit the reported rules to those with right-side items that appear in the lookup table. Specify either all or none of these options.

rightlookupdatabase

The database containing the right-side lookup table.

rightlookuptable

The right-side lookup table.

rightlookupcolumn

The column to match with right-side items in rules.

sequencecolumn

[Optional] The input table column that specifies sequencing for input items, typically a date or timestamp.

Default behavior: The function does no sequence analysis.

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'