UseCases/Vantage_Analytics_Library/VAL_Analytics_and_ML_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Vantage Analytic Library Demo \n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Analytic Algorithms and Scoring</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following functions are currently available in the Vantage and are a part of the Vantage Analytic Library.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Matrix Building</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Matrix Building builds a sum-of-squares-and-cross-products (SSCP) matrix or other derived matrix type from a table in a Teradata Database. Matrix Building does this by generating and running the SQL to call\n",
    "the Teradata CALCMATRIX table operator.  The resulting matrix can be (re)used by Linear Regression or Factor analysis.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Linear Regression</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Linear Regression is one of the fundamental types of predictive modelling algorithms. In linear regression, a dependent numeric variable is expressed in terms of the sum of one or more independent numeric variables, which are each multiplied by a numeric coefficient, usually with a constant term added to the sum of independent variables. Linear Regression consists of the coefficients of the independent variables together with a constant term that comprise a linear regression model. Applying these coefficients to the variables (columns) of each observation (row) in a data set (table) is known as scoring.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Factor Analysis - PCA</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Factor Analysis is one of the most fundamental types of statistical analysis, and Principal Components Analysis (PCA), is arguably the most common variety of Factor Analysis.  In Factor Analysis, a set of variables (in this case columns) is reduced to a smaller number of factors that account for most of the variance in the variables.  This can be useful in reducing the number of variables by converting them to factors, or in gaining insight into the nature of the variables when they are used for further data analysis. Additionally, the Factor Analysis scoring process expresses each factor as a linear combination of the input columns.   The score output table contains one or more index (key) columns and factor score columns, one for each factor.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Logistic Regression</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression is one of the most widely used types of statistical analysis.  In Logistic Regression, a set of independent variables (in this case columns) is processed to predict the value of a dependent variable (column) that assumes two values referred to as response (1) and non-response (0).  Actually, the user specifies what value of the dependent variable to treat as the response, and all other values assumed by the dependent variable are treated as non-respsonse.  The result is not however a continuous numeric variable as seen in Linear Regression, but rather a probability between 0 and 1 that the response value is assumed by the dependent variable.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Decision Trees</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Currently, the Teradata Warehouse Miner External Stored Procedure provides decision trees for classification models. They are built largely on the techniques described in [Quinlan] and as such, splits using information gain ratio are provided. Pruning is also provided, also using the gain ratio technique. The concept of Information gain ratio is simple - the more you know about a topic, the less new information you are apt to get about it. To be more concise: If you know an event is very probable, it is no surprise when it happens - that is, it gives you little information that it actually happened.  Taking this a bit further, we can formulate that the amount of information gained is inversely proportional to the probability of an event happening. Given that entropy refers to the probability of an event occurring, we can also say that as the entropy increases, the information gain decreases. A decision tree scoring function is provided to score and/or evaluate a decision tree model.</p> \n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>K-Means Clustering</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The task of modelling multidimensional data sets encompasses a variety of statistical techniques, including that of ‘cluster analysis’. Cluster analysis is a statistical process for identifying homogeneous groups of data objects.  K-Means clustering is one of the simplest and popular unsupervised machine learning algorithms.  Unsupervised algorithms make inferences from datasets using only input without known, or labelled, outcomes.  The objective of K-means is simple: group similar data points together and discover underlying patterns. To achieve this objective, K-means looks for a fixed number (k) of clusters in a dataset.  A cluster refers to a collection of data points aggregated together because of certain similarities.  The algorithm requires as input a target number k, which refers to the number of centroids to identify in the dataset, where a centroid is the location representing the center of the cluster.  Every data point is allocated to each of the clusters through reducing the in-cluster sum of squares.  In other words, the K-means algorithm identifies k number of centroids, and then allocates every data point to the nearest cluster, while keeping the centroids as small as possible. The ‘means’ in the K-means refers to averaging of the data; that is, finding the centroid.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Association Rules/Sequence Analysis</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Association Rules provide various measures concerning items residing in groups.  The measures, support, confidence, lift and Z Score, help to determine the likelihood that one or more items exist in a group, given that another one or more items exist in the same group.  The classic example of this type of study is market basket analysis, in which the groups are shopping carts and the items are the products purchased in the shopping carts.  A sequence analysis may be optionally requested, wherein the sequence of items matters, ordering the items on each side of each rule, with left side items preceding the right side items.</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Vantage Analytic Library - Call Structure</b></p>\n",
    "\n",
    "call val.td_analyze('\\<function name\\>','\\<database=database name\\>;\\<tablename=table name\\>;\\<columns=column1, column2\\>,'\\<param1\\>=\\<value1\\>;\\<param2\\>=\\<value2\\>...');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b>1. Connect to Vantage </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press Enter, then use down arrow to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Setup for execution of notebook. Begin running steps with Shift + Enter keys.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Set query_band='DEMO=VAL_Analytics_and_ML_SQL.ipynb;' update for session;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'> <b>2. Getting Data for This Demo:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are in the following cell, and one of them is commented out. You may switch between the modes by changing the comment string.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "---call get_data('DEMO_Financial_cloud'); ---takes about 45 seconds\n",
    "call get_data('DEMO_Financial_local'); ---takes about 100 second"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – if you want to see status of databases/tables created and space used.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call space_report();  -- optional, takes about 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Demo data - Financial Customers/Accounts/Transactions</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following data has been put into the DEMO_Financial database for the examples in the three different Jupyter Notebooks.  It's a simplistic fictitious dataset of banking customers (approx. 10K rows), Accounts (approx. 20K rows) and Transactions (approx. 1Million rows).  They are related to each other in the following ways:</p>\n",
    "\n",
    "<img src=\"images/DemoData.png\"/>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Financial.Customer;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The below command displays metadata for the table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%meta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Financial.Accounts;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DEMO_Financial.Transactions;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following Analytic Data Set (ADS) was created by joining all three tables above:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE VAL_ADS AS (\n",
    "    SELECT \n",
    "        T1.cust_id  AS cust_id\n",
    "       ,MIN(T1.income) AS tot_income\n",
    "       ,MIN(T1.age) AS tot_age\n",
    "       ,MIN(T1.years_with_bank) AS tot_cust_years\n",
    "       ,MIN(T1.nbr_children) AS tot_children\n",
    "       ,CASE WHEN MIN(T1.marital_status) = 1 THEN 1 ELSE 0 END AS single_ind\n",
    "       ,CASE WHEN MIN(T1.gender) = 'F' THEN 1 ELSE 0 END AS female_ind\n",
    "       ,CASE WHEN MIN(T1.marital_status) = 2 THEN 1 ELSE 0 END AS married_ind\n",
    "       ,CASE WHEN MIN(T1.marital_status) = 3 THEN 1 ELSE 0 END AS separated_ind\n",
    "       ,MAX(CASE WHEN T1.state_code = 'CA' THEN 1 ELSE 0 END) AS ca_resident_ind\n",
    "       ,MAX(CASE WHEN T1.state_code = 'NY' THEN 1 ELSE 0 END) AS ny_resident_ind\n",
    "       ,MAX(CASE WHEN T1.state_code = 'TX' THEN 1 ELSE 0 END) AS tx_resident_ind\n",
    "       ,MAX(CASE WHEN T1.state_code = 'IL' THEN 1 ELSE 0 END) AS il_resident_ind\n",
    "       ,MAX(CASE WHEN T1.state_code = 'AZ' THEN 1 ELSE 0 END) AS az_resident_ind\n",
    "       ,MAX(CASE WHEN T1.state_code = 'OH' THEN 1 ELSE 0 END) AS oh_resident_ind\n",
    "       ,MAX(CASE WHEN T2.acct_type = 'CK' THEN 1 ELSE 0 END) AS ck_acct_ind\n",
    "       ,MAX(CASE WHEN T2.acct_type = 'SV' THEN 1 ELSE 0 END) AS sv_acct_ind\n",
    "       ,MAX(CASE WHEN T2.acct_type = 'CC' THEN 1 ELSE 0 END) AS cc_acct_ind\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CK' THEN T2.starting_balance+T2.ending_balance ELSE 0 END) AS ck_avg_bal\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'SV' THEN T2.starting_balance+T2.ending_balance ELSE 0 END) AS sv_avg_bal\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CC' THEN T2.starting_balance+T2.ending_balance ELSE 0 END) AS cc_avg_bal\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CK' THEN T3.principal_amt+T3.interest_amt ELSE 0 END) AS ck_avg_tran_amt\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'SV' THEN T3.principal_amt+T3.interest_amt ELSE 0 END) AS sv_avg_tran_amt\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CC' THEN T3.principal_amt+T3.interest_amt ELSE 0 END) AS cc_avg_tran_amt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 1 THEN T3.tran_id ELSE NULL END) AS q1_trans_cnt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 2 THEN T3.tran_id ELSE NULL END) AS q2_trans_cnt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 3 THEN T3.tran_id ELSE NULL END) AS q3_trans_cnt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 4 THEN T3.tran_id ELSE NULL END) AS q4_trans_cnt\n",
    "    FROM DEMO_Financial.Customer AS T1\n",
    "        LEFT OUTER JOIN DEMO_Financial.Accounts AS T2\n",
    "            ON T1.cust_id = T2.cust_id\n",
    "        LEFT OUTER JOIN DEMO_Financial.Transactions AS T3\n",
    "            ON T2.acct_nbr = T3.acct_nbr\n",
    "GROUP BY T1.cust_id) WITH DATA UNIQUE PRIMARY INDEX (cust_id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM VAL_ADS ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Matrix Building</b></p>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Matrix Building builds a sum-of-squares-and-cross-products (SSCP) matrix or other derived matrix type  from a table in a Teradata Database. Matrix Building does this by generating and running the SQL to call\n",
    "the Teradata CALCMATRIX table operator provided in Teradata beginning with the 14.10 release. The results are stored either in a table or as a result set returned to the user. The purpose in building a matrix depends on the type of matrix built. For example, when a correlation matrix is built, view it to determine the correlations or relationships between the various columns in the matrix.  For more information about the CALCMATRIX table operator, see Teradata® Database SQL Functions, Operators, Expressions, and Predicates, B035-1145,</p>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columns**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns comprising the created matrix or matrices. The columns must reside in the table named with the tablename parameter, residing in the database named with the database parameter.  For example: columns=column1,column2,column3.  When columns=all is entered, all columns in the input table are analyzed.  Other options include allnumeric.  Do not use the following column names, as these are reserved for use by the CALCMATRIX table  operator: rownum, rowname, c, or s.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input table to build a matrix from.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **Matrix**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Matrix parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **groupby**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If specified, group by columns divide the input table into parts, one for each combination of values in the group by columns. For each combination of values a separate matrix is built, though they are all stored in the same output table or result data set. The group by columns must reside in the table named with the tablename parameter. The default case is no group by columns.  For example:  groupby=column1,column2,column3.  Do not use the column names rownum, rowname, c, or s, as these are reserved for use by the CALCMATRIX table operator.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixoutput {COLUMNS|VARBYTE}**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Matrix output can either be returned as COLUMNS in a table or as VARBYTE values, one per column, in a reduced output table. The default is output returned as COLUMNS.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixtype**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following types of matrix can be built with this function. If not specified, a sum-of-squares-andcross- products (SSCP) matrix is built.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    \n",
    "    - SSCP = sum-of-squares-and-cross-products matrix \n",
    "    - ESSCP = Extended-sum-of-squares-and-cross-products matrix (the default)\n",
    "    - CSSCP = Corrected-sum-of-squares-and-cross-products matrix\n",
    "    - COV = Covariance matrix\n",
    "    - COR = Correlation matrix\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **nullhandling {ZERO|IGNORE}**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a value in a selected column is NULL, the row that contains the NULL value is by default omitted from processing (nullhandling=IGNORE), or the value may be replaced in calculations with zero through the use of this parameter (nullhandling=ZERO).</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database that contains the resulting matrix output table.  If outputdatabase and outputtablename are not both specified, a volatile output table with randomly generated name is created in the logon userdatabase and the results are returned to the user in a result data set.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the output table representing one or more matrices. If group by columns are specified, there is a matrix for each combination of group by column values.  Note that the output table must first be dropped by the user before executing the function if outputdatabase and outputtablename are both specified. If outputdatabase and outputtablename are not both specified, a volatile output table with randomly generated name is created in the logon userdatabase, and the result set is returned to the user instead.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **where**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>An optional conditional expression may be specified with this parameter, limiting the amount of data used to build the matrix.  For example: where=income>0</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columnstoexclude**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a column specifier such as all is used in the columns parameter, the columnstoexclude parameter may be used to exclude specific columns from the analysis.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  In this example, input columns age, years_with_bank, and nbr_children are used to build a 3-by-3 SSCP matrix. No permanent output table is created, just a result data set that is returned to the user.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=DEMO_Financial;\n",
    "                          tablename=Customer;\n",
    "                          columns=age,years_with_bank,nbr_children');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above output has the output columns as below(Table columns in bold comprise the Unique Primary Index (UPI).)\n",
    "</p>\n",
    "\n",
    " <table style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Column</th>\n",
    "    <th>DataType</th>\n",
    "    <th>Description</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>rownum</td>\n",
    "    <td>Integer</td>  \n",
    "    <td>Row number.</td>\n",
    "  </tr>  \n",
    "  <tr>\n",
    "    <td>rowname</td>\n",
    "    <td>Varchar</td>  \n",
    "    <td>Column names specified by columns.</td>\n",
    "  </tr>  \n",
    "  <tr>\n",
    "    <td>c</td>\n",
    "    <td>Integer</td>  \n",
    "    <td>[Column appears only if matrixtype = ESSCP.]\n",
    "        <li>Count, an extension of Sum of Squares.</li>\n",
    "    </td>\n",
    "  </tr>  \n",
    "  <tr>\n",
    "    <td>s</td>\n",
    "    <td>Integer</td>  \n",
    "    <td>[Column appears only if matrixtype = ESSCP.]\n",
    "        <li>Sum, an extension of Sum of Squares.</li>\n",
    "    </td>\n",
    "  </tr>  \n",
    "  <tr>\n",
    "    <td>column_name</td>\n",
    "    <td>Varchar</td>  \n",
    "    <td>[Column appears once for each column specified by columns.]</td>\n",
    "  </tr>    \n",
    "</table>\n",
    "   \n",
    "<p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2.  In this example, we create a matrix on all columns in the VAL_ADS table and include a null handling parameter so that NULL values are replaced with zeros.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=all;\n",
    "                          nullhandling=zero'); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above output all columns are displayed and the NULL values are replaced with zeros.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>3.  In this example, an output table is requested to persist a copy of the created matrix.  Note that you must select the data out of the created matrix table in order to view it, since a result set is not returned to the user.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix', \n",
    "                         'database=DEMO_Financial;\n",
    "                          tablename=Customer;\n",
    "                          columns=age,years_with_bank,nbr_children;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=_matrix1b');\n",
    "                          "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM demo_user._matrix1b ORDER BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function does not return a result set. To see the matrix table, use a SELECT statement. The next select statement displays the columns from the table created in the previous CALL statement.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>4.  In this example, an SQL WHERE clause is requested to limit the amount of data passed to the CALCMATRIX table operator.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=DEMO_Financial;\n",
    "                          tablename=Customer;\n",
    "                          columns=age,years_with_bank,nbr_children;\n",
    "                          where=nbr_children > 1');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The CALL procedure returns 2 Matrixes in One Result Set.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>5.  In this example, an SQL GROUP BY clause is added to the requested matrix so that two matrices are built and returned in the same result set (for gender=’F’ and gender=’M’).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=DEMO_Financial;\n",
    "                          tablename=Customer;\n",
    "                          columns=age,years_with_bank,nbr_children;\n",
    "                          groupby=gender');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The CALL procedure returns the result columns using Group by gender.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>6.  In each of the following examples, one matrix of each type is created on the entire VAL_ADS table and returned to the user as a result set. The matrix type used are :- COR, COV, CCSCP, ESSCP and SSCP.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output for the below calls varies based on the matrix type.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The details for each type of outputs can be found at the link <a href ='https://docs.teradata.com/r/Enterprise_IntelliFlex_Lake_VMware/Vantage-Analytics-Library-User-Guide/Analytic-Algorithms-and-Scoring-Functions/Matrix-Building/Results-Data'>here.</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=all;\n",
    "                          matrixtype=COR');                         \n",
    "                                                   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=all;\n",
    "                          matrixtype=COV');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=all;\n",
    "                          matrixtype=CSSCP');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=all;\n",
    "                          matrixtype=ESSCP');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('matrix',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=all;\n",
    "                          matrixtype=SSCP');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Linear Regression</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Linear Regression is one of the fundamental types of predictive modelling algorithms. In linear regression, a dependent numeric variable is expressed in terms of the sum of one or more independent numeric variables, which are each multiplied by a numeric coefficient, usually with a constant term added to the sum of independent variables. Linear Regression is the coefficients of the independent variables together with a constant term that comprise a linear regression model. Applying these coefficients to the variables (columns) of each observation (row) in a data set (table) is known as scoring, as described in Linear Regression Scoring.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Some of the key features of VAL version of linear regression are outlined below.</p>\n",
    "\n",
    "<p>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "- The Teradata supplied table operator CALCMATRIX is used to build a table that represents an extended cross-products matrix that is the input to the algorithm</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>- One or more group by columns may optionally be specified so that an input matrix is built for each combination of group by column values, and subsequently a separate linear model is built for each matrix. To achieve this, the names of the group by columns are passed to CALCMATRIX as parameters, so it includes them as columns in the matrix table it creates.</li>\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>- The algorithm is partially scalable because the size of each input matrix depends only on the number of independent variables (columns) and not on the size of the input table. The calculations performed on the client workstation however are not scalable when group by columns are used, because each model is built serially based on each matrix in the matrix table.</li>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columns**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns representing the independent variables used in building a linear regression model.  The columns must reside in the table named with the tablename parameter, residing in the database named with the database parameter.  For example, columns=c1,c2,c3. When columns=all is entered, all columns in the input table are analyzed.  Other options include allnumeric.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **dependent**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the column that represents the dependent variable.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input table to build a predictive model from.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **Linear**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Linear parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>    \n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **constant**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Set to true if the linear model includes a constant term or false otherwise. The default value is true.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **groupby**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns dividing the input table into partitions, one for each combination of values in the group by columns. For each partition or combination of values a separate linear model is built.  The columns must reside in the table named with the tablename parameter. The default case is no group by columns.  For example: groupby=column1,column2,column3</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database where the matrix table resides if specified, as indicated by the matrixtablename parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Instead of internally building a matrix with the Matrix function each time this analysis is performed, the user may build an ESSCP Matrix once with the Matrix Analysis and save it to a table with this name in matrixdatabase.  The matrix can subsequently be read from this table instead of re-building it each time.  If the matrix table is specified, the columns specified with the columns parameter may be a subset of the columns in this matrix and may be specified in any order.  The columns must however all be present in the matrix.  Further, if group by columns are specified in the matrix, these same group by columns must be specified in this analysis.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database that contains the resulting output table that represents one or more linear models.  If outputdatabase and outputtablename are not both specified, a volatile output table with randomly generated name is created in the logon user database.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the output table representing one or more linear models. A second output table reporting statistical measures is automatically named on the user’s behalf by appending _rpt to the end of this name. These two output tables represent a single linear model with coefficients and statistical measures in the absence of group by columns, or if group by columns are specified, there is a model for each combination of group by column values in these output tables.  Note that both of the output tables must first be dropped by the user if outputdatabase and outputtablename are both specified. If outputdatabase and outputtablename are not both specified, volatile output tables with randomly generated names are created in the logon user database, and the two output result sets are returned to the user instead.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **neardependencyreport**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table if specified.  Two threshold parameters are available for  this report, conditionindexthreshold (default 30) and varianceproportionthreshold (default 0.5).  The report is included in the XML output only if collinearity is detected.</p>  \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **conditionindexthreshold**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table if specified.  One of the threshold parameters for that report is conditionindexthreshold with a default value of 30.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **varianceproportionthreshold**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table, if specified.  One of the threshold parameters available for this report is varianceproportionthreshold with a default value of 0.5.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columnstoexclude**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a column specifier such as all is used in the columns parameter, the columnstoexclude parameter may be used to exclude specific columns from the analysis.  For convenience, when the columnstoexclude parameter is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included as columnstoexclude.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  Using the VAL_ADS table, build a linear model to estimate average monthly balance (cc_avg_bal) that a banking customer has on their credit card based on all non-credit card related variables in the table.  Coefficients and model variable statistics are created in the LinearRegressionDemo1 table as specified by the outputtablename argument.  Note that model statistics are created in the LinearRegressionDemo1_rpt table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('linear',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=tot_age,tot_income,tot_cust_years,tot_children,single_ind,female_ind,married_ind,separated_ind,ck_acct_ind,sv_acct_ind,sv_avg_bal,ck_avg_bal,ca_resident_ind,ny_resident_ind,tx_resident_ind,il_resident_ind,az_resident_ind,oh_resident_ind;\n",
    "                          dependent=cc_avg_bal;\n",
    "                          neardependencyreport=true;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LinearRegressionDemo1');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LinearRegressionDemo1 order by 2 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output of the call above is shown in the answer set above.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The details of the output can be found at the link <a href ='https://docs.teradata.com/r/Enterprise_IntelliFlex_Lake_VMware/Vantage-Analytics-Library-User-Guide/Analytic-Algorithms-and-Scoring-Functions/Linear-Regression/Results-Data'>here.</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LinearRegressionDemo1_rpt order by 2 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report in the answer set below, double click on the contents of the 'html' colum returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=LinearRegressionDemo1;\n",
    "                           analysistype=linear');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2)  In order to showcase the \"group by\" feature of Linear Regression, lets build a model for each state_code;  to do this, the original ADS was first modifying to include state_code instead of the state indicator variables:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE MULTISET TABLE VAL_ADS2 AS (\n",
    "    SELECT \n",
    "        T1.cust_id  AS cust_id\n",
    "       ,MIN(T1.income) AS tot_income\n",
    "       ,MIN(T1.age) AS tot_age\n",
    "       ,MIN(T1.years_with_bank) AS tot_cust_years\n",
    "       ,MIN(T1.nbr_children) AS tot_children\n",
    "       ,CASE WHEN MIN(T1.marital_status) = 1 THEN 1 ELSE 0 END AS single_ind\n",
    "       ,CASE WHEN MIN(T1.gender) = 'F' THEN 1 ELSE 0 END AS female_ind\n",
    "       ,CASE WHEN MIN(T1.marital_status) = 2 THEN 1 ELSE 0 END AS married_ind\n",
    "       ,CASE WHEN MIN(T1.marital_status) = 3 THEN 1 ELSE 0 END AS separated_ind\n",
    "       ,MAX(CASE WHEN T1.state_code = 'CA' THEN 'CA'\n",
    "                 WHEN T1.state_code = 'NY' THEN 'NY'\n",
    "                 WHEN T1.state_code = 'TX' THEN 'TX'\n",
    "                 WHEN T1.state_code = 'IL' THEN 'IL'\n",
    "                 WHEN T1.state_code = 'AZ' THEN 'AZ'\n",
    "                 WHEN T1.state_code = 'OH' THEN 'OH' ELSE 'OTHER' END) AS state_code\n",
    "       ,MAX(CASE WHEN T2.acct_type = 'CK' THEN 1 ELSE 0 END) AS ck_acct_ind\n",
    "       ,MAX(CASE WHEN T2.acct_type = 'SV' THEN 1 ELSE 0 END) AS sv_acct_ind\n",
    "       ,MAX(CASE WHEN T2.acct_type = 'CC' THEN 1 ELSE 0 END) AS cc_acct_ind\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CK' THEN T2.starting_balance+T2.ending_balance ELSE 0 END) AS ck_avg_bal\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'SV' THEN T2.starting_balance+T2.ending_balance ELSE 0 END) AS sv_avg_bal\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CC' THEN T2.starting_balance+T2.ending_balance ELSE 0 END) AS cc_avg_bal\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CK' THEN T3.principal_amt+T3.interest_amt ELSE 0 END) AS ck_avg_tran_amt\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'SV' THEN T3.principal_amt+T3.interest_amt ELSE 0 END) AS sv_avg_tran_amt\n",
    "       ,AVG(CASE WHEN T2.acct_type = 'CC' THEN T3.principal_amt+T3.interest_amt ELSE 0 END) AS cc_avg_tran_amt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 1 THEN T3.tran_id ELSE NULL END) AS q1_trans_cnt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 2 THEN T3.tran_id ELSE NULL END) AS q2_trans_cnt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 3 THEN T3.tran_id ELSE NULL END) AS q3_trans_cnt\n",
    "       ,COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 4 THEN T3.tran_id ELSE NULL END) AS q4_trans_cnt\n",
    "    FROM DEMO_Financial.Customer AS T1\n",
    "        LEFT OUTER JOIN DEMO_Financial.Accounts AS T2\n",
    "            ON T1.cust_id = T2.cust_id\n",
    "        LEFT OUTER JOIN DEMO_Financial.Transactions AS T3\n",
    "            ON T2.acct_nbr = T3.acct_nbr\n",
    "GROUP BY T1.cust_id) WITH DATA UNIQUE PRIMARY INDEX (cust_id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM VAL_ADS2 ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, build a linear regression model for each state_code.  Coefficients and variable statistics are created within the LinearRegressionDemo2 table as specified by the outputtablename argument.  Note that model statistics are created withing the LinearRegressionDemo2_rpt table, one per each state_code.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('linear',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS2;\n",
    "                          columns=tot_age,tot_income,tot_cust_years,tot_children,single_ind,married_ind,separated_ind,female_ind,ck_acct_ind,sv_acct_ind,sv_avg_bal,ck_avg_bal;\n",
    "                          dependent=cc_avg_bal;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LinearRegressionDemo2;\n",
    "                          groupby=state_code');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LinearRegressionDemo2 ORDER BY 1, 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output of the call above is shown in the answer set above.\n",
    "The details of the output can be found at the link <a href ='https://docs.teradata.com/r/Enterprise_IntelliFlex_Lake_VMware/Vantage-Analytics-Library-User-Guide/Analytic-Algorithms-and-Scoring-Functions/Linear-Regression/Results-Data'>here.</a></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LinearRegressionDemo2_rpt ORDER BY 1, 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' column returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=LinearRegressionDemo2;\n",
    "                           analysistype=linear');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Linear Regression Scoring</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Linear Regression Scoring is the application of a Linear Regression model to an input table that containsthe same independent variable columns contained in the model. The result is an output score table that minimally contains one or more key columns and an estimate of the dependent variable in the model. The user may also choose to perform model evaluation, either separately or in combination with scoring. When requested, a report is produced as a result data set containing the standard error of estimate as well as the minimum, maximum, and average absolute error. When model evaluation is requested, the input table must\n",
    "contain a column representing the dependent variable in the model. When both scoring and evaluation are requested, the output table automatically includes the residual value, calculated as the difference between the original value and the predicted value of the dependent variable. The residual value can also be requested when only scoring is performed.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeldatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the model input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeltablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input table containing the linear model to use in scoring. This table must be created using the linear function, named with the outputtablename parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input table to score.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **Linearscore**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Linearscore parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **index**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By default, the primary index columns of the score output table are the primary index columns of the input table. This parameter allows the user to specify one or more different columns for the primary index of the score output table. Regardless of whether the user uses the default setting or specifies different columns, the index columns are included both in the Primary Index clause and the select list. In addition, the index columns needs to form a unique key for the score output table. Otherwise, there are more than one score for a given observation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database that contains the output score table. If outputdatabase and outputtablename are not both specified, a volatile output table with randomly generated name is created in the logon user database.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the score output table containing key columns and predicted values of the dependent variable in the linear model. The output table may also contain retained columns passed through from the input to the output table unchanged, as well as a residual value containing the difference between the actual and predicted values of the dependent variable column. The output table may also contain group by columns if these are present in the model table.  If the output table exists, it must first be dropped by the user if outputdatabase and outputtablename are both specified. If outputdatabase and outputtablename are not both specified because only model evaluation is being performed, a volatile output table with a randomly generated name is created in the logon user database, and the output result set is returned to the user instead.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **predicted**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If the score method is score or score and evaluate, the name of the predicted value column is entered here. If not entered here, the name of the dependent column in the input table is used.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **residual**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If the score method is score and evaluate, the name of a column that contains the residual value (the difference between the predicted and actual value of the dependent variable) is given here.  By default, this column is named “Residual”.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **retain**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>One or more columns from the input table can optionally be specified here to be passed along to the score output table.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **gensqlonly**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When true, the SQL for the requested function is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **samplescoresize**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When a scoring function produces a score table, the user has the option to view a sample of the rows using the \"samplescoresize=n\" parameter, where n is an integer number of rows to view in a result set.  Cases where a sample is not returned include when you are only generating SQL and when you are only evaluating (i.e. not scoring).  By default, a sample of output score rows is not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **scoringmethod**</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Three scoring methods are available as outlined below. By default, the model is scored but not evaluated.\n",
    "    - Score\n",
    "    - Evaluate\n",
    "    - Score and Evaluate\n",
    "</p>    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1) First, lets score the single Linear Regression model created above - for demonstration purposes, we'll use the same VAL_ADS table to score.  The minimum, maximum and average absolute error, and the standard error of estimate are returned as a result set.  The scored data set includes the actual along with the prediction and the residual as we are evaluating as well.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('linearscore',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=LinearRegressionDemo1;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LinearRegressionScore1;\n",
    "                          predicted=estimate;\n",
    "                          retain=cc_avg_bal;\n",
    "                          scoringmethod=scoreandevaluate;');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM demo_user.LinearRegressionScore1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2) Next lets score the multiple state_code Linear Regression models again using VAL_ADS2 table for demonstration purposes.  For each model, the minimum, maximum and average absolute error, and the standard error of estimate are returned as a result set.  The scored data set includes the actual along with the state_code, prediction and the residual as we are evaluating as well.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('linearscore',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS2;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=LinearRegressionDemo2;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LinearRegressionScore2;\n",
    "                          predicted=estimate;\n",
    "                          retain=cc_avg_bal;\n",
    "                          scoringmethod=scoreandevaluate;');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LinearRegressionScore2 ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The above outputs of the Linear Regression Scoring have the below details.The following table is built in the requested Output Database by Linear Regression scoring. Note the following considerations when viewing the table:\n",
    "    <li>The options selected affect the structure of the table.</li>\n",
    "    <li>The columns in bold comprise the Unique Primary Index (UPI).</li>\n",
    "    <li>There may be repeated groups of columns.</li>\n",
    "    <li>Some columns are generated only if specific options are selected.</li>\n",
    "</p>\n",
    "\n",
    " <table style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>Cloumn</th>\n",
    "    <th>DataType</th>\n",
    "    <th>Description</th>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Key</td>\n",
    "    <td>User-Defined</td>  \n",
    "    <td>One or more unique-key columns that defaults to the index defined in the table to be scored (that is, in tablename). The data type defaults to the same as the scored table, but can be changed using Primary Index Columns.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td><app_var></td>\n",
    "    <td>User-Defined</td>  \n",
    "    <td>One or more columns as specified with the retain parameter.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>Groupby Variable</td>\n",
    "    <td>User-Defined</td>  \n",
    "    <td>A column is generated for each groupby column. Within each column there are distinct values of the groupby columns for which a linear model was built.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td><dep_var></td>\n",
    "    <td>Float</td>  \n",
    "    <td>If scoringmethod is score or scoreandevaluate, the name of the predicted value column can be entered using the predicted parameter. If not entered, the name of the dependent column in the input table is used.</td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>residual_column_name</td>\n",
    "    <td>Float</td>  \n",
    "    <td>[Column appears only if scoringmethod=scoreandevaluate.] Residual values of the evaluation, the difference between the estimated value and the actual value of the dependent variable.</td>\n",
    "  </tr>      \n",
    "</table>\n",
    "   \n",
    "<p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Factor Analysis - PCA</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Factor Analysis is one of the most fundamental types of statistical analysis, and Principal Components Analysis (PCA), is arguably the most common variety of Factor Analysis.  In Factor Analysis, a set of variables (in this case columns) is reduced to a smaller number of factors that account for most of the variance in the variables.  This can be useful in reducing the number of variables by converting them to factors, or in gaining insight into the nature of the variables when they are used for further data analysis. Additionally, the Factor Analysis scoring process expresses each factor as a linear combination of the input columns.   The score output table contains one or more index (key) columns and factor score columns, one for each factor.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Some of the key features of version of Factor Analysis are outlined below:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- The Teradata supplied table operator CALCMATRIX is used to build a table that represents a correlation matrix for input to Factor Analysis.  To avoid rebuilding this matrix every time the algorithm is run, the user may run the Matrix Analysis separately, saving an ESSCP matrix in a table that can then be input to Factor Analysis.  Refer to the matrixdatabase and matrixtablename parameters.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- One or more group by columns may optionally be specified so that an input matrix is built for each combination of group by column values, and subsequently a separate Factor Analysis model is built for each matrix.  To achieve this, the names of the group by columns are passed to CALCMATRIX as parameters, so it includes them as columns in the matrix table it creates.  Refer to the groupby parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- A Near Dependency Report is available to identify two or more columns that may be collinear.  This report can be requested by setting parameter neardependencyreport=true and if desired, conditionindexthreshold (default 30) and varianceproportionthreshold (default 0.5).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- Both orthogonal and oblique factor rotations are available.  Refer to the rotationtype parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- There are three Prime Factor reports available.  Refer to parameters factorloadingsreport, factorvariablesreport and factorvariablesloadingsreport.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- The algorithm is partially scalable because the size of each input matrix depends only on the number of independent variables (columns) and not on the size of the input table. The calculations performed on the client workstation however are not scalable when group by columns are used, because each model is built serially based on each matrix in the matrix table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columns**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns representing the variables used in building a factor analysis model.  The columns must reside in the table named with the tablename parameter, residing in the database named with the database parameter.  For example, columns=c1,c2,c3. When columns=all is entered, all columns in the input table are analyzed.  Other options include allnumeric.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input table to build a factor model from.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **Factor**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Factor parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **conditionindexthreshold**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table if specified.  One of the threshold parameters for that report is conditionindexthreshold with a default value of 30.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **eigenmin**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The minimum eigenvalue to include factors for.  The default is 1.0.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **flr or factorloadingsreport**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Prime Factor Loadings Report in which rows are variables and columns are factors, matching each variable with the factor it has the biggest absolute loading value with.  To request it, set factorloadingsreport=true, or flr=true.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **fvlr or factorvariablesloadingsreport**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Prime Factor Variables with Loadings report is equivalent to the Prime Factor Variables report with the addition of the loading values that determined the relationship between factors and variables. The absolute sizes of the loading values point out the relationship strength and the sign its direction, i.e. either a positive or negative correlation. To request it, set factorvariablesloadingsreport=true, or fvlr=true.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **fvr or factorvariablesreport**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Prime Factor Variables Report in which rows are variables and columns are factors, matching variables with their prime factors, and if a threshold is used, possibly other than prime factors.  (Either a threshold percent may be specified with the thresholdpercent parameter, or a threshold loading may be specified with the thresholdloading parameter.) To request it, set factorvariablesreport=true, or fvr=true.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **gamma**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a factor rotation is requested of the type orthomax or orthomin, a parameter in the rotation equation called gamma must be set by the user using this parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **groupby**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns dividing the input table into partitions, one for each combination of values in the group by columns. For each partition or combination of values a separate factor model is built.  The columns must reside in the table named with the tablename parameter. The default case is no group by columns.  For example:   groupby=column1,column2</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database where the matrix table resides if specified, as indicated by the matrix tablename parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Instead of internally building a matrix with the Matrix function each time a Factor Analysis is performed, the user may build an ESSCP Matrix once with the Matrix Analysis and save it to a table with this name in matrixdatabase.  The matrix can subsequently be read from this table instead of re-building it each time.  If the matrix table is specified, the columns specified with the columns parameter may be a subset of the columns in this matrix and may be specified in any order.  The columns must however all be present in the matrix.  Further, if group by columns are specified in the matrix, these same group by columns must be specified in the Factor Analysis.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixtype**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The type of matrix for processing can be either correlation or covariance, affecting measure and score scaling. The default is correlation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **neardependencyreport**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table if specified.  Two threshold parameters are available for  this report, conditionindexthreshold (default 30) and varianceproportionthreshold (default 0.5).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database that contains the resulting output table that represents one or more factor models.  If outputdatabase and outputtablename are not both specified, a volatile output table with randomly generated name is created in the logon user database.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the output table representing one or more factor models. A second output table reporting statistical measures is automatically named on the user’s behalf by appending \"_rpt\" to the end of this name.  If outputdatabase and outputtablename are not both specified, volatile output tables with randomly generated names are created in the logon user database, and the two output result sets are returned to the user instead.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **rotationtype**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Various schemes are provided for rotating factors for possibly better results.  Both orthogonal and oblique rotations are provided.  The default is rotationtype=none.  A parameter in the rotation equation, called below gamma, assumes a different value for each rotation type, with f the number of factors and v the number of variables.  In some cases, the user sets the value of gamma using the td_analyze parameter of the same name.</p>\n",
    "\n",
    "\n",
    "\n",
    "<table style = 'font-size:14px;font-family:Arial;color:#00233C'>\n",
    "  <tr>\n",
    "    <th>rotationtype</th>\n",
    "    <th>gamma value</th>\n",
    "    <th>orthogonal / oblique</th>\n",
    "    <th>notes</th>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>equamax</td>\n",
    "    <td>f / 2</td>\n",
    "    <td>orthogonal</td>\n",
    "    <td> </td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>orthomax</td>\n",
    "    <td>set by user</td>\n",
    "    <td>orthogonal</td>  \n",
    "    <td> </td>\n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>parsimax</td>\n",
    "    <td>v ( f – 1 )  /  ( v + f + 2)</td>\n",
    "    <td>orthogonal</td>\n",
    "    <td> </td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>quartimax</td>\n",
    "    <td>0.0</td>\n",
    "    <td>orthogonal</td>\n",
    "    <td> </td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>varimax</td>\n",
    "    <td>1.0</td>\n",
    "    <td>orthogonal</td>\n",
    "    <td> </td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>biquartimin</td>\n",
    "    <td>0.5</td>\n",
    "    <td>oblique</td>\n",
    "    <td> </td>  \n",
    "  </tr> \n",
    "  <tr>\n",
    "    <td>covarimin</td>\n",
    "    <td>1.0</td>\n",
    "    <td>oblique</td>\n",
    "    <td>least oblique rotation</td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>orthomin</td>\n",
    "    <td>set by user</td>\n",
    "    <td>oblique</td>\n",
    "    <td> </td>  \n",
    "  </tr>\n",
    "  <tr>\n",
    "    <td>quartimin</td>\n",
    "    <td>0.0</td>\n",
    "    <td>oblique</td>\n",
    "    <td>most oblique rotation</td>  \n",
    "  </tr>  \n",
    "</table>\n",
    "</p>   \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdloading**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When the Prime Factor Variables Report is selected, variables are matched with their prime factors, and if the threshold value in this parameter is used, possibly other than prime factors.  Specifically, if a threshold factor loading is specified, a factor that is not a prime factor may be associated with a variable.  For example, thresholdloading=0.5.  Note that thresholdloading and thresholdpercent may not both be specified.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdpercent**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When the Prime Factor Variables Report is selected, variables are matched with their prime factors, and if the threshold value in this parameter is used, possibly other than prime factors.  Specifically, if a threshold percent less than 1.0 is specified, a factor that is not a prime factor may be associated with a variable.  For example, thresholdpercent=0.9.  Note that thresholdloading and thresholdpercent may not both be specified.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **varianceproportionthreshold**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table, if specified.  One of the threshold parameters available for this report is varianceproportionthreshold with a default value of 0.5.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columnstoexclude**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a column specifier such as all is used in the columns parameter, the columnstoexclude parameter may be used to exclude specific columns from the analysis.  For convenience, when the columnstoexclude parameter is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included as columnstoexclude.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  Using the VAL_ADS table, perform a factor analysis with all reporting options.  In this case, the model is stored in an XML string (xmlmodel) within the outputtablename specified.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('factor',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=tot_age,tot_income,tot_cust_years,tot_children,single_ind,married_ind,separated_ind,female_ind,ck_acct_ind,sv_acct_ind,sv_avg_bal,ck_avg_bal,ca_resident_ind,ny_resident_ind,tx_resident_ind,il_resident_ind,az_resident_ind,oh_resident_ind;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=FactorAnalysisOut1;\n",
    "                          flr=true;fvr=true;fvlr=true');\n",
    "                          "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM demo_user.FactorAnalysisOut1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To extract the XML in the xmlmodel column into a viewable format, run the following query.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT XMLSERIALIZE(Content X.Dot) as XMLText\n",
    "FROM (SELECT * FROM demo_user.FactorAnalysisOut1) AS C,\n",
    "XMLTable (\n",
    "'//*'\n",
    "PASSING CREATEXML(C.XmlModel)\n",
    ") AS X (\"Dot\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=FactorAnalysisOut1;\n",
    "                           analysistype=factor');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report in the answer set below, double click on the contents of the 'html' colum returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2.  Using the VAL_ADS2 table, perform a factor analysis with all reporting options, grouping by state_code, creating a factor model for each state.  In this case, the model is stored in an XML string (xmlmodel) within the outputtablename specified for each state_code.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('factor',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS2;\n",
    "                          columns=tot_age,tot_income,tot_cust_years,tot_children,single_ind,married_ind,separated_ind,female_ind,ck_acct_ind,sv_acct_ind,sv_avg_bal,ck_avg_bal;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=FactorAnalysisOut2;\n",
    "                          flr=true;fvr=true;fvlr=true;\n",
    "                          groupby=state_code');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM FactorAnalysisOut2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To extract the XML in the xmlmodel column into a viewable format, run the following query.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT XMLSERIALIZE(Content X.Dot) as XMLText\n",
    "FROM (SELECT * FROM demo_user.FactorAnalysisOut2) AS C,\n",
    "XMLTable (\n",
    "'//*'\n",
    "PASSING CREATEXML(C.XmlModel)\n",
    ") AS X (\"Dot\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=FactorAnalysisOut2;\n",
    "                           analysistype=factor');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report in the answer set below, double click on the contents of the 'html' colum returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Factor Analysis Scoring</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When Factor Analysis is performed and a factor model created based on columns in an input table,  another table containing the same columns can be used to create a table of factor scores.  The scoring process expresses each factor as a linear combination of the input columns.   The score output table contains one or more index (key) columns and factor score columns, one for each factor.  It may also contain columns that have been “retained” from the score input table.  When factor analysis was based on a correlation matrix, scoring input data is normalized by subtracting the mean and dividing by the standard deviation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Some of the key features of Factor Scoring are outlined below.\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>Factor Scoring is performed entirely by generating and executing SQL.  An option is available however to generate the scoring SQL and not execute it.  Refer to the gensqlonly parameter.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>A random sample of the score output table may by requested and returned in a result set.</li>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial;color:#00233C'>If multiple factor models were built by means of one or more group by columns, the resulting score table will include these columns and score the grouped input columns accordingly.</li>\n",
    "\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the input table to score.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeldatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the factor model table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeltablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the table containing the factor model to use in scoring, built by the Factor Analysis function.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **FactorScoring**</b></p>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'>\n",
    "    The FactorScoring parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **index**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By default, the primary index columns of the score output table are the primary index columns of the input table. This parameter allows the user to specify one or more columns for the primary index of the score output table. Regardless of whether the user uses the default setting or specifies different columns, the index columns are included both in the Primary Index clause and the select list. In addition, the index columns should form a unique key for the score output table, or there will be more than one score for a given observation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **gensqlonly**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When true, the SQL for the requested function is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database where the output score table will be built.  (If the scoring method is not evaluate,  this parameter is required.)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the output score table to be built.  (If the scoring method is not evaluate,  this parameter is required.)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **retain**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>One or more columns from the input table can optionally be specified here to be passed along to the score output table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **samplescoresize**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When a scoring function produces a score table, the user has the option to view a sample of the rows using the \"samplescoresize=n\" parameter, where n is an integer number of rows to view in a result set.  Cases where a sample is not returned include when you only generating SQL and when you are only evaluating (i.e. not scoring).  By default, a sample of output score rows is not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **scoringmethod**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Three scoring methods are available as outlined below. By default, the model is scored but not evaluated, as requested in this manner:  scoringmethod=score.\n",
    "    - score\n",
    "    - evaluate\n",
    "    - scoreandevaluate\n",
    "</p>    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  Lets score the single Factor Analysis created above.  The scores for each factor, for every customer identifier is returned as a result set.  An XML report is also returned for the factor model with an \"_rpt\" extension</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('factorscore','database=demo_user;tablename=VAL_ADS;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=FactorAnalysisOut1;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=FactorAnalysisScore1;\n",
    "                          samplescoresize=10;\n",
    "                          scoringmethod=scoreandevaluate');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' column returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=FactorAnalysisScore1;\n",
    "                           analysistype=factorscore');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2.  Next lets score the multiple Factor Analysis created above creating factors by state_code.  The scores for each factor, for every customer identifier by state_code is returned as a result set. An XML report is also returned for the factor model with an \"_rpt\" extension</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('factorscore',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS2;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=FactorAnalysisOut2;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=FactorAnalysisScore2;\n",
    "                          samplescoresize=10;\n",
    "                          scoringmethod=scoreandevaluate');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' colum returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=FactorAnalysisScore2;\n",
    "                           analysistype=factorscore');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Logistic Regression</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Logistic Regression is one of the most widely used types of statistical analysis.  In Logistic Regression, a set of independent variables (in this case columns) is processed to predict the value of a dependent variable (column) that assumes two values referred to as response (1) and non-response (0).  Actually, the user specifies what value of the dependent variable to treat as the response, and all other values assumed by the dependent variable are treated as non-response.  The result is not however a continuous numeric variable as seen in Linear Regression, but rather a probability between 0 and 1 that the response value is assumed by the dependent variable.  There are many types of analysis that lend themselves to the use of Logistic Regression, and when scoring a model, benefit from the estimation of a probability rather than a fixed value. For example, when predicting who should be targeted for a marketing campaign, the scored customers can be ordered by the predicted probability from most to least likely, and the top n values taken from the customer list.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Some of the key features of this version of Logistic Regression are outlined below:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- The Teradata supplied table operator CALCMATRIX is used to build an ESSCP matrix for purposes of validating the input data, such as by checking for constant values.  Also, to avoid rebuilding this matrix every time the algorithm is run, the user may run the Matrix Analysis separately, saving an ESSCP matrix in a table that can then be input to Logistic Regression.  Refer to the matrixdatabase and matrixtablename parameters.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- One or more group by columns may optionally be specified so that an input matrix is built for each combination of group by column values, and subsequently a separate Logistic Regression model is built for each matrix.  To achieve this, the names of the group by columns are passed to CALCMATRIX as parameters, so it includes them as columns in the matrix table it creates.  Refer to the groupby parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- The stepwise feature for Logistic Regression is a technique for selecting the independent variables in a logistic model.  It consists of different methods of “trying” variables and adding or removing them from a model through a series of forward and backward steps described in the parameter section.  The following parameters are used with this feature, as described in the section of Optional Parameters.\n",
    "    - stepwise\n",
    "    - forward\n",
    "    - forwardonly\n",
    "    - backward\n",
    "    - backwardonly\n",
    "    - enter\n",
    "    - remove\n",
    "</p>    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- A Statistics Table is available, displaying the mean and standard deviation of each model variable.  Refer to the statstable parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- A Success Table is available, displaying counts of predicted versus actual values of the dependent variable in the logistic model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- A Multi-Threshold Success Table is available.  Refer to the thresholdtable parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- A Lift Table, such as would be used to build a Lift Chart, is available.  Refer to the lifttable parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- A Near Dependency Report is available to identify two or more columns that may be collinear.  This report can be requested by setting parameter neardependencyreport=true and if desired, conditionindexthreshold (default 30) and varianceproportionthreshold (default 0.5).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- The algorithm is partially scalable because the size of each input matrix depends only on the number of independent variables (columns) and not on the size of the input table. The calculations performed on the client workstation however are not scalable when group by columns are used, because each model is built serially based on each matrix in the matrix table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columns**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns representing the independent variables used in building a logistic regression model.  The columns must reside in the table named with the tablename parameter, and within the database specified by the database parameter. For example, columns=c1,c2,c3. When columns=all is entered, all columns in the input table are analyzed.  Other options include allnumeric.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **dependent**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the column acting as the dependent variable that is being predicted.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **Logistic**</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    The logistic parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input table to build a logistic regression model from.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **backward**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Backward steps, i.e. removing variables from a model, use the P-value of the T-statistic, i.e. the ratio of a B-coefficient to its standard error.  The variable (column) with the largest P-value is removed if the P-value exceeds the criterion to remove.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **backwardonly**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This technique is similar to the backward technique except that a forward step is not performed.  It starts with all independent variables in the model.  Backward steps are executed until no more are possible.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **conditionindexthreshold**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table that ends in “_txt”.  One of the threshold parameters for that report is conditionindexthreshold with a default value of 30.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **constant**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By default, constant=true so that a constant term is included in the logistic model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **convergence**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The convergence criterion such that the algorithm stops iterating when the change in log likelihood function falls below this value.  By default, this value is .001.</p>   \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **enter**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The criterion to enter a variable into the model.  The W-statistic chi-square P-value must be less than this value for a variable to be added.  The default value is 0.05.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **forward**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this technique, starting with no independent variables in the model, a forward step is performed, adding the “best” choice, followed by a backward step, removing the “worst” choice.  Refer to the stepwise parameter for a description of the steps in this technique.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **forwardonly**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This technique is similar to the forward technique except that a backward step is not performed.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **groupby**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns dividing the input table into partitions, one for each combination of values in the group by columns.  For each partition or combination of values a separate logistic model and XML report object is built.  The group by columns must reside in the table named with the tablename parameter. The default case is no group by columns.  For example:   groupby=column1,column2</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **lifttable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A table of information, such as would be required to build a lift chart is available.  It splits up the computed probability values into deciles with the usual counts and percentages to demonstrate what happens when more and more rows of ordered probabilities are accumulated.  It is delivered in the function’s XML output string</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database where the matrix table resides if specified, as indicated by the matrixtablename parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **matrixtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Instead of internally building a matrix with the Matrix function each time this analysis is performed, the user may build an ESSCP Matrix once with the Matrix Analysis and save it to a table with this name in matrixdatabase.  The matrix can subsequently be read from this table instead of re-building it each time.   If the matrix table is specified, the columns specified with the columns parameter may be a subset of the columns in this matrix and may be specified in any order.  The columns must however all be present in the matrix.  Further, if group by columns are specified in the matrix, these same group by columns must be specified as such in this analysis.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **maxiterations**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The maximum number of attempts to converge on a solution.  By default, this value is 100.</p>   \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **neardependencyreport**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table if specified.  Two threshold parameters are available for this report, conditionindexthreshold (default 30) and varianceproportionthreshold (default 0.5).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database that contains the resulting output table that represents one or more logistic models.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the output table representing one or more logistic models. A second output table reporting statistical measures is automatically named on the user’s behalf by appending “_rpt” to the end of this name.  A third XML output table containing requested reports is automatically named on the user’s behalf by appending “_txt” to the end of this name.  If outputdatabase and outputtablename are not both specified, volatile output tables with randomly generated names are created in the logon user database, and the two output result sets are returned to the user instead.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **remove**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The criterion to remove a variable from the model.  The T-Statistic P-value must be greater than this value for a variable to be removed. The default value is 0.05.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **response**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The value assumed by the dependent column that is to be treated as the response value.  For example, if the dependent column is gender, the response value might be set to ‘F’ using response=Y.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **statstable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>An optional data quality report, delivered in the function’s XML output string, including the mean and standard deviation of each model variable, derived from an ESSCP matrix.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **stepwise**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When stepwise=true the stepwise procedure is performed.  By default, stepwise=false.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- Forward steps, i.e. adding variables to a model, add the variable with the smallest chi-square P-value connected to its special W-statistic, provided the P-value is less than the criterion to enter.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>- Backward steps, i.e. removing variables from a model, use the P-value of the T-statistic, i.e. the ratio of a B-coefficient to its standard error.  The variable (column) with the largest P-value is removed if the P-value exceeds the criterion to remove.</p>\n",
    "    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **successtable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A table delivered in the function’s XML output string, displaying counts of predicted versus actual values of the dependent variable of the logistic regression model.  The default is to not produce a successtable.  (This report is similar to the Decision Tree Confusion Matix, but the Successtable only includes two values of the dependent variable, namely response versus non-response.)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdbegin**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The beginning threshold value utilized in the Multi-Threshold Success Table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdend**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ending threshold value utilized in the Multi-Threshold Success Table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdincrement**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The difference in threshold values between adjacent rows in the Multi-Threshold Success Table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdtable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When this parameter is set to true, the Multi-Threshold Success Table is produced and included in the XML output string in the result table.  The default is thresholdtable=false.  This report can be thought of as a table where each row is a Prediction Success Table, and each row has a different threshold value as generated by the thresholdbegin, thresholdend and thresholdincrement parameters.  What is meant by a threshold here is the value above which the predicted probability indicates a response.</p> \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **varianceproportionthreshold**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If neardependencyreport=true, an XML report showing columns that may be collinear is produced and stored in the output table that ends in “_txt”.  One of the threshold parameters for that report is varianceproportionthreshold with a default value of 0.5.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columnstoexclude**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a column specifier such as all is used in the columns parameter, the columnstoexclude parameter may be used to exclude specific columns from the analysis.  For convenience, when the columnstoexclude parameter is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included as columnstoexclude.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1) Once again using the VAL_ADS table, lets build a logistic regression model to predict the customer bases propensity to open a credit card account (cc_acct_ind) based upon all non-credit card variables in the analytic data set.  The model coefficients and variable statistics are created within the outputtablename specified.  Additionally, model statistics are created within a table with a \"_rpt\" extension on the outputtablename.  The reports for the successtabpe, thresholdtable and lifttable are returned in an XML string within a table with a \"_txt\" extension on the outputtablename.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('logistic',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=tot_age,tot_income,tot_cust_years,tot_children,single_ind,married_ind,separated_ind,female_ind,ck_acct_ind,sv_acct_ind,sv_avg_bal,ck_avg_bal,ca_resident_ind,ny_resident_ind,tx_resident_ind,il_resident_ind,az_resident_ind,oh_resident_ind;\n",
    "                          dependent=cc_acct_ind;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LogisticOut1;\n",
    "                          statstable=true;\n",
    "                          successtable=true;\n",
    "                          thresholdtable=true;\n",
    "                          lifttable=true;');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LogisticOut1 ORDER BY 2 DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LogisticOut1_rpt ORDER BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' column returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=LogisticOut1;\n",
    "                           analysistype=logistic');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2) Now using the VAL_ADS2 table, lets build a logistic regression model to predict the customer bases propensity to open a credit card account (cc_acct_ind) based upon all non-credit card variables in the analytic data set, creating a model for each state_code using the groupby=state_code option.   The model coefficients and variable statistics are created within the outputtablename specified for each state_code model.  Additionally, model statistics are created within a table with a \"_rpt\" extension on the outputtablename.  The reports for the successtabpe, thresholdtable and lifttable are returned in an XML string within a table with a \"_txt\" extension on the outputtablename.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('logistic',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS2;\n",
    "                          columns=tot_age,tot_income,tot_cust_years,tot_children,single_ind,married_ind,separated_ind,female_ind,ck_acct_ind,sv_acct_ind,sv_avg_bal,ck_avg_bal;\n",
    "                          dependent=cc_acct_ind;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LogisticOut2;\n",
    "                          statstable=true;\n",
    "                          successtable=true;\n",
    "                          thresholdtable=true;\n",
    "                          lifttable=true;\n",
    "                          groupby=state_code');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LogisticOut2 ORDER BY 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM LogisticOut2_rpt ORDER BY 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' colum returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=LogisticOut2;\n",
    "                           analysistype=logistic');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>9. Logistic Regression Scoring</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>After building a predictive model using the Logistic Regression function, the model can be passed to a Logistic Regression Scoring function for creation of a score table containing predicted values of the dependent variable.  This is done by reading the outputdatabase and outputtablename created by the Logistic Regression function, referring to them here as modeldatabase and modeltablename respectively.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In addition to a score table, this function can optionally produce the following results.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "- Success Table\n",
    "- Multi-Threshold Success Table\n",
    "- Lift Table\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the table to analyze.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **logisticscore**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The logisticscore parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeldatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the table representing the logistic regression model input to the analysis.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeltablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>The table containing the logistic regression model that is used to score the data.  It must reside in the database indicated by the modeldatabase parameter.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the output table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output table containing the predicted values of the dependent variable.  It must reside in the database indicated by the outputdatabase parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The table containing the columns to analyze, representing the dependent and independent variables in the analysis.  It must reside in the database indicated by the database parameter.</p>\n",
    "\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **estimate**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of a column in the score output table containing the estimated value of the dependent variable (column).  Note that either estimate or probability must be requested.  Also note that if the estimate column is not unique in the score output table, ‘_tm_’ is automatically placed in front of the name.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **gensqlonly**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When true, the SQL for the requested function is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **index**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By default, the primary index columns of the score output table are the primary index columns of the input table. This parameter allows the user to specify one or more columns for the primary index of the score output table. Regardless of whether the user uses the default setting or specifies different columns, the index columns are included both in the Primary Index clause and the select list. In addition, the index columns should form a unique key for the score output table, or there will be more than one score for a given observation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **lifttable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A table of information, such as would be required to build a lift chart is available.  It splits up the computed probability values into deciles with the usual counts and percentages to demonstrate what happens when more and more rows of ordered probabilities are accumulated.  It is delivered in the function’s XML output string</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true or not set, the output table is dropped before creating a new one.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **probability**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of a column in the score output table containing the probability that the dependent value is equal to the resonse value.  Note that either estimate or probability must be requested.  Also note that if the probability column is not unique in the score output table, ‘_tm_’ is automatically placed in front of the name.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **retain**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>One or more columns from the input table can optionally be specified here to be passed along to the score output table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **samplescoresize**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When a scoring function produces a score table, the user has the option to view a sample of the rows using the \"samplescoresize=n\" parameter, where n is an integer number of rows to view in a result set.  Cases where a sample is not returned include when you are only generating SQL and when you are only evaluating (i.e. not scoring).  By default, a sample of output score rows is not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **scoringmethod**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Three scoring methods are available as outlined below. By default, the model is scored but not evaluated, as requested in this manner:  scoringmethod=score.\n",
    "    - score\n",
    "    - evaluate\n",
    "    - scoreandevaluate\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **successtable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A table delivered in the function’s XML output string, displaying counts of predicted versus actual values of the dependent variable of the model.  The default is to not produce a success table.  (This report is like the Decision Tree Confusion Matrix, but the Success Table only includes two values of the dependent variable, response versus non-response.)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdbegin**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The beginning threshold value utilized in the Multi-Threshold Success Table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdend**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ending threshold value utilized in the Multi-Threshold Success Table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdincrement**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The difference in threshold values between adjacent rows in the Multi-Threshold Success Table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **thresholdtable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When this parameter is set to true, the Multi-Threshold Success Table is produced and included in the XML output string in the result table.  The default is thresholdtable=false.  This report can be thought of as a table where each row is a Prediction Success Table, and each row has a different threshold value as generated by the thresholdbegin, thresholdend and thresholdincrement parameters.  What is meant by a threshold here is the value above which the predicted probability indicates a response.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  First lets score the single Logistic Regression model created above.  Lets also create Lift and Success Tables, populated within a table with _score_txt appended to the model table name.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('logisticscore',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=LogisticOut1;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LogisticScore1;\n",
    "                          estimate=Estimate;\n",
    "                          probability=Probability;\n",
    "                          retain=cc_acct_ind;\n",
    "                          samplescoresize=25;\n",
    "                          lifttable=true;\n",
    "                          successtable=true;  \n",
    "                          scoringmethod=scoreandevaluate');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' column returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=LogisticScore1;\n",
    "                           analysistype=logisticscore');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2.  Next lets score the multiple Logistic Regression model created above for each state.  In this case, we can not evaluate the model by creating Lift and Success Tables.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('logisticscore',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS2;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=LogisticOut2;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=LogisticScore2;\n",
    "                          estimate=Estimate;\n",
    "                          probability=Probability;\n",
    "                          retain=cc_acct_ind;\n",
    "                          samplescoresize=25;\n",
    "                          scoringmethod=score');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - No reports for Logistic Scoring with GROUPBY option...</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>10. Gain Ratio Decision Trees</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Currently, the Teradata Warehouse Miner External Stored Procedure provides decision trees for classification models. They are built largely on the techniques described in [Quinlan] and as such, splits using information gain ratio are provided. Pruning is also provided, also using the gain ratio technique. The concept of Information gain ratio is simple - the more you know about a topic, the less new information you are apt to get about it. To be more concise: If you know an event is very probable, it is no surprise when it happens - that is, it gives you little information that it actually happened.  Taking this a bit further, we can formulate that the amount of information gained is inversely proportional to the probability of an event happening. Given that entropy refers to the probability of an event occuring, we can also say that as the entropy increases, the information gain decreases. A decision tree scoring function is provided to score and/or evaluate a decision tree model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columns**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The independent input columns used in decision tree building. These columns must reside in the table named with the tablename parameter, residing in the database named with the database parameter.  For example: columns=column1,column2,column3.  When columns=all is entered, all columns in the input table are analyzed.  Other options include allnumeric.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **decisiontree**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Identifies the type of function being performed. The decisiontreescore parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **dependent**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The dependent parameter is the name of a column whose values are being predicted. The dependent column is selected from the available columns that reside in the table specified by the database and tablename parameters.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input table to build a predictive model from.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **algorithm**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The algorithm the decision tree uses during building. Currently this option only allows gainratio.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **binning**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Option to automatically Bincode the continuous independent variables. Continuous data is separated into one hundred bins when this option is selected. If the variable has fewer than one hundred distinct values, this option is ignored. Default is false.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **max_depth**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Specifies the maximum number of levels the tree can grow. The default is 100.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **min_records**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Specifies how far the decision tree can split. Unless a node is pure (meaning it has only observations with the same dependent value) it splits if each branch that can come off this node contains at least this many observations. The default is a minimum of two cases for each branch.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **operatordatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database where the table operators called by td_analyze reside. If not specified, the database software searches the standard search path for table operators, including the current user database.  For example: operatordatabase=val</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the resulting output table when outputstyle=table or view.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the output table representing the decision tree model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **pruning**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Determines the style of pruning to use after the tree is fully built. The default option is gainratio. The only other option at this time is none which does no pruning of the tree.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columnstoexclude**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a column specifier such as all is used in the columns parameter, the columnstoexclude parameter may be used to exclude specific columns from the analysis.  For convenience, when the columnstoexclude parameter is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included as columnstoexclude.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1) Using the VAL_ADS analytic dataset, build a decision tree model to predict propensity of a customer to open a credit card based upon non-credit card related variables.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('decisiontree',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          columns=tot_age,tot_income,tot_cust_years,tot_children,single_ind,married_ind,separated_ind,female_ind,ck_acct_ind,sv_acct_ind,sv_avg_bal,ck_avg_bal,ca_resident_ind,ny_resident_ind,tx_resident_ind,il_resident_ind,az_resident_ind,oh_resident_ind;\n",
    "                          dependent=cc_acct_ind;\n",
    "                          min_records=2;\n",
    "                          max_depth=5;\n",
    "                          binning=false;\n",
    "                          algorithm=gainratio;\n",
    "                          pruning=gainratio;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=DecisionTree1;\n",
    "                          operatordatabase=val;');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' column returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=DecisionTree1;\n",
    "                           analysistype=decisiontree');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>11. Decision Tree Scoring</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In order to deploy the gain ratio decision tree model created above, a companion decision tree scoring function is provided to score and/or evaluate a decision tree model.  In addition to a score table, this function can optionally produce the following results.\n",
    "\n",
    "- Confidence Factors\n",
    "- Targeted Binary Confidence\n",
    "- Confusion Matrix\n",
    "- Profile Tables\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the table to analyze.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **decisiontreescore**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The decisiontreescore parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeldatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the table representing the decision tree model input to the analysis.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeltablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The table containing the decision tree model in PMML format that is used to score the data.  It must reside in the database indicated by the modeldatabase parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the output table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output table containing the predicted values of the dependent variable.  It must reside in the database indicated by the outputdatabase parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The table containing the columns to analyze, representing the dependent and independent variables in the analysis.  It must reside in the database indicated by the database parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **confusionmatrix**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A table delivered in the function’s XML output string, displaying counts of predicted versus actual values of the dependent variable of the decision tree model.   It also contains counts of correct and incorrect predictions.  The default is to not produce a confusion matrix.  (This report is similar to the Logistic Regression Success Table, but that table only includes two values of the dependent variable, or response versus non-response.)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **gensqlonly**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When true, the SQL for the requested function is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **includeconfidence**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If selected, the output table will contain a column indicating how likely it is, for a particular leaf node on the tree, that the prediction is correct.  (This option may not be combined with the targetedvalue parameter.)</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **index**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>By default, the primary index columns of the score output table are the primary index columns of the input table. This parameter allows the user to specify one or more columns for the primary index of the score output table. Regardless of whether the user uses the default setting or specifies different columns, the index columns are included both in the Primary Index clause and the select list. In addition, the index columns should form a unique key for the score output table, or there will be more than one score for a given observation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true or not set, the output table is dropped before creating a new one.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **predicted**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If the ‘scoringmethod’ parameter is set to ‘score’ or ‘scoreandevaluate’, the name of the predicted value column is entered here. If not entered here, the name of the dependent column in the input table is used.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **profiletables**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When selected, a set of two tables are created that can capture decision rules for a given customer or prediction.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **retain**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>One or more columns from the input table can optionally be specified here to be passed along to the score output table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **samplescoresize**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When a scoring function produces a score table, the user has the option to view a sample of the rows using the \"samplescoresize=n\" parameter, where n is an integer number of rows to view in a result set.  Cases where a sample is not returned include when you only generating SQL and when you are only evaluating (i.e. not scoring).  By default, a sample of output score rows is not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **scoringmethod**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Three scoring methods are available as outlined below. By default, the model is scored but not evaluated, as requested in this manner:  scoringmethod=score.\n",
    "    - score\n",
    "    - evaluate\n",
    "    - scoreandevaluate\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **targetedvalue**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If selected, the output table will contain a column indicating how likely it is, for a particular leaf node and targeted value of a predicted result with only two values, that the prediction is correct. (This option may not be combined with the targetedvalue parameter.)</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1) Using the same VAL_ADS analytic dataset for demonstration purposes, score the decision tree model built above that predicts the propensity of a customer to open a credit card based upon non-credit card related variables.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('decisiontreescore',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=DecisionTree1;\n",
    "                          outputdatabase=demo_user;\n",
    "                          predicted=Predicted;\n",
    "                          retain=cc_acct_ind;\n",
    "                          outputtablename=DecisionTreeScore1;\n",
    "                          scoringmethod=scoreandevaluate;\n",
    "                          includeconfidence=true;');                          "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DecisionTreeScore1 ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Note - To view HTML report, double click on the contents of the 'html' column returned by calling 'report';  alternately,  right click on the contents of the 'html' column and select 'Show Cell as Text...', or copy the contents of the cell and create a HTML report.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze ('report',\n",
    "                          'database=demo_user;\n",
    "                           tablename=DecisionTreeScore1;\n",
    "                           analysistype=decisiontreescore');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>12. K-Means Clustering</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The task of modeling multidimensional data sets encompasses a variety of statistical techniques, including that of ‘cluster analysis’. Cluster analysis is a statistical process for identifying homogeneous groups of data objects.  K-Means clustering is one of the simplest and popular unsupervised machine learning algorithms.  Unsupervised algorithms make inferences from datasets using only input without known, or labelled, outcomes.  The objective of K-means is simple: group similar data points together and discover underlying patterns. To achieve this objective, K-means looks for a fixed number (k) of clusters in a dataset.  A cluster refers to a collection of data points aggregated together because of certain similarities.  The algorithm requires as input a target number k, which refers to the number of centroids to identify in the dataset, where a centroid is the location representing the center of the cluster.  Every data point is allocated to each of the clusters through reducing the in-cluster sum of squares.  In other words, the K-means algorithm identifies k number of centroids, and then allocates every data point to the nearest cluster, while keeping the centroids as small as possible.The ‘means’ in the K-means refers to averaging of the data; that is, finding the centroid.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The first parameter for clustering is the Kmeans function name, followed by clustering parameters.  Fast K-Means Clustering returns two data sets that are viewed as result sets.  The first result set is a progress report with two columns, a timestamp and a progress message. Use the result to see how the algorithm converged and what made it stop processing.  The second result set contains cluster means and variances. Specifically, the rows associated with positive cluster IDs contain the average values of each of the clustered columns along with the count for each cluster ID. The rows associated with negative cluster IDs contain the variance of each of the clustered columns for each cluster ID.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columns**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The input columns used in clustering. The columns must reside in the table named with the tablename parameter, residing in the database named with the database parameter. For example, columns=c1,c2,c3. When columns=all is entered, all columns in the input table are analyzed.  Other options include allnumeric.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **Kmeans**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Identifies the type of function being performed.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **kvalue**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The number of clusters to be contained in the cluster model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database to contain the resulting output table that represents a cluster model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the output table representing the cluster model.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the table containing the data to cluster.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **continuation**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If true, clustering begins with values determined by pre-existing result tables rather than random values. The default is false.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **iterations**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The maximum number of iterations to perform during modeling. The default is 50.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **operatordatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database where the table operators called by td_analyze reside. If not specified, the database software searches the standard search path for table operators, including the current user database.  For example: operatordatabase=val</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **threshold**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A decimal value used to determine if the algorithm has converged based on how much the cluster centroids change from one iteration to the next. The default is .001.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columnstoexclude**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a column specifier such as all is used in the columns parameter, the columnstoexclude parameter may be used to exclude specific columns from the analysis.  For convenience, when the columnstoexclude parameter is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included as columnstoexclude.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  For this example, we use the same ADS, but will perform a Z-Score transformation on the columns we are going to cluster representing the number of standard deviations away from the mean each data point is.  This transformed data set will be used below to perform K-Means clustering.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CALL val.td_analyze('vartran',\n",
    "                         'database=demo_user;\n",
    "                          tablename=VAL_ADS;\n",
    "                          outputstyle=view;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=ClusterInput;\n",
    "                          keycolumns=cust_id;\n",
    "                          zscore=columns(tot_income,tot_age,tot_cust_years);\n",
    "                          retain=columns(cust_id);');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM ClusterInput;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2) Now perform K-Means Clustering on the transformed dataset (VIEW).  The result set returned are status messages per iteration, along with the cluster centroids and variances.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('Kmeans',\n",
    "                         'database=demo_user;\n",
    "                          tablename=ClusterInput;\n",
    "                          columns=tot_income,tot_age,tot_cust_years;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=ClusterModel;\n",
    "                          operatordatabase=val;\n",
    "                          kvalue=3;\n",
    "                          iterations=5;\n",
    "                          threshold=0.1;');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>13. K-Means Cluster Scoring</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>After building a model using the Fast K-Means Clustering algorithm, new data is scored using Fast K-Means Cluster Scoring. The first parameter for Fast K-Means Cluster Scoring is the KmeansScore function name,\n",
    "followed by cluster scoring parameters. Fast K-Means Cluster Scoring returns one or two data sets that are viewed as result sets. One result set is a progress report with two columns, a timestamp, and a progress message. The other result set is only returned if the samplescoresize parameter is set. It contains a sampling of the rows in the output score table, the actual number of rows determined by the value of the samplescoresize parameter.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the input table to be scored.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeldatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the table that represents the cluster model to score.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **modeltablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the input table containing the cluster model to score.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **index**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The names of one or more columns in the input table to use as the primary index of the scored output table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **KmeansScore**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Identifies the type of function being performed.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the resulting scored output table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the scored output table to build.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the table containing the data to cluster.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **clustername**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The name of the column representing the cluster identifier. The default is clusterid.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **fallback**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>An optional flag to indicate (true), that the scored output table has the fallback attribute (that is, have a mirrored copy).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **operatordatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database where the table operators called by td_analyze reside. If not specified, the database software searches the standard search path for table operators, including the current user database. For example: operatordatabase=val</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **retain**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A comma-separated list naming columns to include in the scored output table unchanged from their names and values in the input table to be scored.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **samplescoresize**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When a scoring function produces a score table, the user has the option to view a sample of the rows using the \"samplescoresize=n\" parameter, where n is an integer number of rows to view in a result set.  Cases where a sample is not returned include when you are only generating SQL and when you are only evaluating (i.e. not scoring).  By default, a sample of output score rows is not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **columnstoexclude**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If a column specifier such as all is used in the columns parameter, the columnstoexclude parameter may be used to exclude specific columns from the analysis.  For convenience, when the columnstoexclude parameter is used, dependent variable and group by columns, if any, are automatically excluded as input columns and do not need to be included as columnstoexclude.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  Cluster Scoring can now be performed on the cluster model just built.  A sample of 10 scored rows is displayed from the result set with includes the cluster assignment and the customer identifier.  Status messages are also returned in a result set.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('KmeansScore',\n",
    "                         'database=demo_user;\n",
    "                          tablename=ClusterInput;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=ClusterScore;\n",
    "                          index=cust_id;\n",
    "                          modeldatabase=demo_user;\n",
    "                          modeltablename=ClusterModel;\n",
    "                          samplescoresize=10;\n",
    "                          operatordatabase=val;');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>14. Association Rules & Sequence Analysis</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Purpose</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Association Rules provide various measures concerning items residing in groups.  The measures, support, confidence, lift and Z Score, help to determine the likelihood that one or more items exist in a group, given that another one or more items exist in the same group.  The classic example of this type of study is market basket analysis, in which the groups are shopping carts and the items are the products purchased in the shopping carts.  An association rule might indicate the likelihood that a given shopping cart contains oranges, given that it also contains apples.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Association rules consist of a left part and a right part.  The left part consists of one or more items that are given to reside in a group, and the right part is the consequence that one or more items also reside in the given group.  The measures are defined as follows:</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "- Support\n",
    "    Percentage of groups containing the items on the left (left side support), on the right (right side support) or on both sides of a rule (rule support).\n",
    "\n",
    "- Confidence\n",
    "    Percentage of groups containing the left side items that also contain the right side items.\n",
    "\n",
    "- Lift\n",
    "    A measure of how much the probability is raised that the right side items occur in a group given that the left side items occur in the group.\n",
    "\n",
    "- Z Score\n",
    "    A statistical measure of how much the expected and actual values of the number of groups containing all the items in the rule varies.  (Zero means expected and actual are the same.)\n",
    "</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A sequence analysis may be optionally requested, wherein there is a sequence of items defined by a sequencecolumn parameter, ordering the items on each side of each rule, with left side items preceding the right side items.  An option is provided called relaxedordering that may be set to true so that items on the left side and the right side may be in any order provided that all left side items precede all right side items.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Processing is performed in generated SQL based on user input parameters.  Each SQL statement is generated and then executed as processing proceeds.  An output table is created for each requested rule combination (1-to-1, 2-to-1, etc.).  As the last processing step, data is selected from these output tables and returned as a result set for up to three output tables.</p>  \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>An option, gensqlonly, is available to bypass SQL execution and simply return the generated SQL.  When computing Association Rules for very large input tables, it may be worthwhile to use this parameter to examine the SQL for performance characteristics.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Required Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **Association**</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    The Association parameter:\n",
    "    - Is required\n",
    "    - Must be the first parameter\n",
    "    - Is always enclosed in single quotes\n",
    "</p>    \n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **database**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the table to analyze.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **groupcolumn**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The column representing groups in the association rules.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **itemcolumn**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The column representing items in the association rules.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Specifies the name of the database to contain the analysis results table(s).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **tablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The table containing the columns to analyze.</p>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Optional Parameters</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **combinations**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Using the combinations parameter, you may designate how many items are on the left and right side of requested association rules.  You may also request more than one combination.  For example, combinations=11,21 produces an analysis of 1-to-1 and 2-to-1 rules.  If this parameter is not specified, a 1-to-1 analysis is performed by default.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **dropsupporttables**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This parameter requests that all intermediate support tables be dropped at the end of processing.  By default, if this parameter is not specified, or if it is set to true, support tables are dropped.  To retain support tables, set dropsupporttables=false.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **gensqlonly**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When true, the SQL for the requested function is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **groupcount**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The count of the number of groups in the input data is by default calculated by the generated SQL.  Optionally, you may set the group count to a fixed value using this parameter.  This is useful when you are processing a reduced input set saved in a previous run, so that calculations can be based on the number of groups in the original input set and not the reduced set.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **minimumconfidence**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The minimum value that the confidence measure of an association rule must have before it is included in a result table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **minimumlift**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The minimum value that the lift measure of an association rule must have before it is included in a result table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **minimumsupport**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The minimum value that the support measure of an association rule must have before it is included in a result table.  When this parameter is utilized, the size of the input data is reduced, potentially impacting the use of the reducedinputtable parameter.  Use of this parameter also causes listwise deletion to be performed, skipping any input rows that have a null group, item or sequence column value.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **minimumzscore**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The minimum value that the Z Score measure of an association rule must have before it is included in a result table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **orderingprobability**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When sequence analysis is being performed, the user may either allow the algorithm to determine ordering probabilities (the default) or set the “probability of correct ordering” to a non-zero value between 0 and 1. (Setting it to 1 effectively ignores this principle in lift and Z Score calculations.)  For more details, please refer to Association Rules in Chapter 1 in Teradata Warehouse Miner User Guide: Volume 3, Analytic Functions (B035-2302).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **outputtablename**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Specifies the name(s) of the table(s) to store the analysis results. If there is more than one combination requested, a list of names may be supplied here to be matched one-to-one with the combinations.  For example, if combinations=11,21, outputtablename=out11,out21 might be used.  If this parameter is not specified, a default name based on the requested or defaulted combination is used.  For example, if the default combination of 1-to-1 is in effect, the outputtablename is _TWM_1_TO_1_AFFINITY.  Note that the leading “_TWM” will be equal to the resulttableprefix if it is specified.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **overwrite**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When overwrite is set to true (default), the output tables are dropped before creating new ones.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **processtype**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This parameter may be set to all (default), support or recalculate.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>- all  (default)</li>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>All processing is performed, from building support tables to calculating final affinities.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>- support</li>\n",
    "</p>    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The single item support table is built and then processing is halted.  This allows you to view the support table and decide what the minimum support value should be, thus reducing the amount of processing performed.  The single item support table is named _TWM_1_ITEM_SUPPORT and is created in outputdatabase, while the parameter outputtablename is ignored.  (If resulttableprefix is specified, it replaces _TWM in the support table name.)</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>- recalculate</li>\n",
    "    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The final affinity tables are calculated based on support tables already present.  This requires that the dropsupporttables parameter was set to false in a previous run so that the support tables are available for recalculating the final affinities.\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **relaxedordering**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This option may be used in conjunction with sequence analysis, that is when a sequence column is specified.  Relaxed ordering occurs when the items on the left side of an association rule may occur in any order (via the sequence column), and the same is the case with the right side items, provided that all left side items precede all right side items.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **resulttableprefix**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The resulttableprefix is used in naming the intermediate support tables created during processing, as well as in creating default output table names.  The default prefix is \"_TWM\".  If the outputtablename parameter is not specified, this prefix is used to create a name that indicates the combination being calculated, so that, for example,  _TWM_1_TO_1_AFFINITY is the default outputtablename for a 1-to-1 combination.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **sequencecolumn**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The column providing sequencing of input items if sequence analysis is desired.  This might typically be a column of type date or timestamp.  By default, sequence analysis is not performed.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **where**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Specifies the SQL WHERE clause generated within the Association Rules SQL to filter rows selected for analysis.  For example: where=cust_id > 0.  Note that single quotes within the parameter value must be doubled, such as in where=channel <> '' ''.  (Ordinarily, the expression would be where channel <> ' '.  Instead, the expression ends with quote-quote-blank-quote-quote).</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note on the following \"description table options\"</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A description table may be joined with results tables for purposes of making the results easier to understand.  The item descriptions are however only included in the returned result sets and are not included in the output table(s).  If any of these parameters is specified, all of them must be specified.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **descriptiondatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the description table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **descriptiontable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The description table to join with the output data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **descriptionidentifier**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The column in the description table that is joined with the result table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **descriptioncolumn**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The column in the description table that contains descriptive item names.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note on the following \"hierarchy table options\"</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A hierarchy table may be joined with the input table in order to reduce the amount of input data and compute association rules at a different hierarchical level. Use of this option has an impact on the data saved in the Reduced Input Table when that parameter (reducedinputtable) is requested. If any of these parameters is specified, all of them must be specified.  When this option is utilized, listwise deletion is automatically performed, ignoring rows that contain a null group, item or sequence column value.</p>  \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **hierarchydatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the hierarchy table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **hierarchytable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The hierarchy table to join with the input data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **hierarchyitemcolumn**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The lowest level item column in the hierarchy table to be matched with the item column in the input data.</p>\n",
    "\n",
    "- **hierarchycolumn**\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The higher level item in the hierarchy table.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note on the following left-side-lookup table options</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A left-side lookup table may be specified to reduce the rules reported to only those with left-side items that appear in the lookup table. If any of these parameters is specified, all of them must be specified.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **leftlookupdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the left-side lookup table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **leftlookuptable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The left-side lookup table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **leftlookupcolumn**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The column to match with left-side items in rules.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note on the following \"reduced input options\"</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If input to the analysis is reduced by using the minimum support option, a hierarchy table or a WHERE clause, the resulting reduced input table may be saved for further analysis.  Note that this option is not affected by the use of a left or right-side lookup parameter or a minimum confidence, lift or Z Score parameter.  Note also that if further analysis is performed on this table, it may be approriate to use the groupcount parameter.  If any of the following two parameters is specified, all of them must be specified.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **reducedinputdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the reduced input table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **reducedinputtable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The table in which to store reduced input rows.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note on the following \"right-side-lookup table options\"</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A right-side lookup table may be specified to reduce the rules reported to those with right-side items that appear in the lookup table. If any of these parameters is specified, all of them must be specified.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **rightlookupdatabase**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The database containing the right-side lookup table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **rightlookuptable**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The right-side lookup table.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>- **rightlookupcolumn**</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The column to match with right-side items in rules.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>1.  The first example uses a minimal number of parameters and produces an output table with default name:  _TWM_1_TO_1_AFFINITY.  By default, this table is also selected from as a result set.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('Association',\n",
    "                         'database=DEMO_Financial;\n",
    "                          tablename=Credit_Tran;\n",
    "                          groupcolumn=cust_id;\n",
    "                          itemcolumn=channel;\n",
    "                          outputdatabase=demo_user;');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>2.  The second example requests a 1-to-1 and a 2-to-1 analysis, while also requesting 0.1 minimum support. A WHERE clause eliminating rows with blank channel column is also specified. (Note that the blank channel value requires double single quotes, that is quote-quote-blank-quote-quote.)</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('Association',\n",
    "                         'database=DEMO_Financial;\n",
    "                          tablename=Credit_Tran;\n",
    "                          groupcolumn=cust_id;\n",
    "                          itemcolumn=channel;\n",
    "                          minimumsupport=0.1;\n",
    "                          where=channel <> '' '';\n",
    "                          combinations=11,21;\n",
    "                          outputdatabase=demo_user;\n",
    "                          outputtablename=val_example2_11,val_example2_21;');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>3. The third example demonstrates a request for sequence analysis by specifying a sequencecolumn parameter.  It also includes an optional minimumsupport, where and outputtablename parameter.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call val.td_analyze('Association',\n",
    "                         'database=DEMO_Financial;\n",
    "                          tablename=Credit_Tran;\n",
    "                          groupcolumn=cust_id;itemcolumn=channel;sequencecolumn=tran_date;\n",
    "                          minimumsupport=0.1;\n",
    "                          where=channel <> '' '';\n",
    "                          outputdatabase=demo_user;outputtablename=val_example3;');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>15. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'>Clean up work tables to prevent error next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE \"VAL_ADS\";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE \"VAL_ADS2\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note:</b> In case you plan to execute the other notebooks in the VantageAnalyticLibrary , do not run the <b>remove_data</b> procedure below. Also in case you are not removing the databases and tables here , please make sure that you do not execute the <b>get_data</b> procedure in the next VantageAnalyticLibrary notebook.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "call remove_data('DEMO_Financial');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Teradata SQL",
   "language": "Teradata SQL",
   "name": "teradatasql"
  },
  "language_info": {
   "codemirror_mode": "Teradata SQL",
   "file_extension": ".tdrs",
   "mimetype": "application/vnd.teradata.resultset",
   "name": "Teradata SQL",
   "nbconvert_exporter": "",
   "pygments_lexer": "",
   "version": "16.20"
  },
  "toc-autonumbering": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}