Parameterize a Variable Creation Analysis as follows.
- Select TWM_CUSTOMER as the Available Table.
-
Do the following to create a variable:
-
Drag the following column from the Columns list to the Variables tab on the right. Note the variable name defaults to the column name.
- TWM_CUSTOMER.cust_id
-
Drag the following column from the Columns list to the Variables tab on the right. Note the variable name defaults to the column name.
-
Do the following to create a variable:
- Click the New button.
- Drag a Maximum (Aggregation) SQL Element on to the empty argument in the variable.
- Drag Number (Literal) on to the empty argument in the Maximum, and rename the variable acct to "acct".
- Select TWM_ACCOUNTS as the Available Table.
-
Do the following to create a variable:
-
Drag the following columns. Note the variable name will default to the column name.
- TWM_ACCOUNTS.ending_balance
-
Drag the following columns. Note the variable name will default to the column name.
- Drag a Average (Aggregation) SQL Element over ending_balance, and rename the variable "bal".
- Select TWM_TRANSACTIONS as the Available Table.
-
Do the following to create a variable:
-
Drag the following columns. Note the variable name defaults to the column name.
- TWM_TRANSACTIONS.tran_id
-
Drag the following columns. Note the variable name defaults to the column name.
- Drag a Count (Aggregation) SQL Element over tran_id, and rename the variable "nbr_trans".
- Select TWM_ACCOUNTS as the Available Table.
- Go to the INPUT-variables-Dimensions tab.
- Click New three times to create three dimension values and rename them CC, CK, and SV.
- Drag TWM_ACCOUNTS.acct_type to each of the three dimension values.
- Drag an Equals (Comparison) SQL Element on top of each instance of acct_type in the three dimensions.
- Drag a String (Literal) SQL Element into the second argument of Equals.
-
Do the following to specify a string value for the three dimensions:
- Change the Properties of the dimensions CC, CK and SV.
- Specify a string value of CC, CK and SV for each dimension, respectively.
- Modify the Else condition to ELSE ZERO.
- Select TWM_TRANSACTIONS as the Available Table.
- Go to the INPUT-variables-Dimensions tab.
- Click New four times to create four dimension values.
- Drag TWM_ACCOUNTS.tran_date to each of the four dimension values.
- Drag a Quarter of Year (Calendar) SQL Element on top of each instance of tran_date in the four dimension values
- Drag an Equals (Comparison) SQL Element on top of each Quarter of Year instance in the four dimension values.
- Drag a Number (Literal) SQL Element into the second argument of the Equals.
- Specify a number, 1-4, for each of the four dimension values by double-clicking on Number, and entering the values. Rename each dimension value Q1-Q4 accordingly.
- Go to the INPUT-dimensions tab.
-
Apply the dimension values to the variables as follows:
- acct - CK, CC, SV
- bal - CK, CC, SV
- nbr_trans - Q1, Q2, Q3, Q4
- Go to the INPUT-anchor table tab.
- Select TWM_CUSTOMER as the anchor table.
-
Specify the Join Paths from TWM_CUSTOMER to each of the following by clicking the Wizard button. The Wizard will initially display the join path selected in the Join Paths window.
-
From Anchor Table (TWM_CUSTOMER) to TWM_ACCOUNTS
TWM_CUSTOMER.cust_id --> TWM_ACCOUNTS.cust_id
-
From Anchor Table (TWM_CUSTOMER) to TWM_TRANSACTIONS
TWM_CUSTOMER.cust_id --> TWM_ACCOUNTS.cust_id
TWM_ACCOUNTS.acct_nbr --> TWM_TRANSACTIONS.acct_nbr
-
From Anchor Table (TWM_CUSTOMER) to TWM_ACCOUNTS
- Go to the OUTPUT-storage tab.
- Select Store the tabular output of this analysis in the database.
- Specify a Table should be created named twm_tutorials_vc2.
For this example, the Variable Creation Analysis generated the following results. Once again, the SQL is not shown:
Only the first 10 rows shown.
| cust_id | CK_acct | SV_acct | CC_acct | CK_bal | SV_bal | CC_bal | Q1_nbr | Q2_nbr | Q3_nbr | Q4_nbr |
|---|---|---|---|---|---|---|---|---|---|---|
| 1362480 | 1.00 | 1.00 | 1.00 | 54.77 | 196.73 | 4.08 | 113 | 17 | 17 | 10 |
| 1362481 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 0 | 0 | 0 |
| 1362484 | 1.00 | 1.00 | 1.00 | 50.46 | 374.50 | 108.74 | 113 | 27 | 20 | 27 |
| 1362485 | 1.00 | 0.00 | 1.00 | 26.34 | 0.00 | 463.16 | 13 | 18 | 50 | 90 |
| 1362486 | 1.00 | 1.00 | 0.00 | 1656.14 | 58.12 | 0.00 | 12 | 10 | 13 | 15 |
| 1362487 | 1.00 | 1.00 | 1.00 | 707.41 | 2.38 | 481.00 | 17 | 25 | 25 | 36 |
| 1362488 | 1.00 | 0.00 | 0.00 | 122.42 | 0.00 | 0.00 | 26 | 39 | 27 | 7 |
| 1362489 | 1.00 | 1.00 | 1.00 | 79.60 | 52.69 | 4.49 | 56 | 51 | 44 | 5 |
| 1362492 | 1.00 | 0.00 | 1.00 | 443.84 | 0.00 | 476.92 | 3 | 42 | 64 | 21 |
| 1362496 | 0.00 | 1.00 | 0.00 | 0.00 | 251.06 | 0.00 | 3 | 3 | 3 | 3 |