Parameterize a Variable Creation Analysis as follows.
- Select TWM_CUSTOMER as the Available Table.
Create seven variables by double-clicking on the following columns. Note that the variable name will default to the column name.
- Select TWM_CREDIT_TRAN as the Available Table.
Create a variable by clicking on New and building up an expression as follows.
Drag an Add (Arithmetic) SQL Element over the Variable, and then drag the following two columns over the empty arguments:
- Drag an Add (Arithmetic) SQL Element over the Variable, and then drag the following two columns over the empty arguments:
- Because there may be negative values, drag and drop an Absolute Value (Arithmetic) SQL Element over both interest_amt and principal_amt.
- Take the average of this expression, by dragging and dropping an Average (Aggregation) on top of the Add.
- Because this analysis may generate many NULL values by joining TWM_CUSTOMER to TWM_CREDIT_TRAN, drag a Coalesce (Case) on top of the Average:
- Drag and drop a Number (Literal) 0 into the expressions folder and rename it from Variable1 to avg_cc_tran_amt to complete the variable.
- Go to INPUT-anchor table and select TWM_CUSTOMER as the anchor table.
- Specify the Join Path from TWM_CUSTOMER to TWM_CREDIT_TRAN by clicking on the Wizard button and specifying that they be joined on the column cust_id.
Go to the OUTPUT-storage tab, and select Store the tabular output of this analysis in the database. Specify that a Table should be created named twm_tutorials_vc1.
For this example, the Variable Creation Analysis generated the following results. Note that the SQL is not shown for brevity.
Only the first 10 columns after sorting are shown.
Variable Creation - Example #1 Data cust_id income age years nbr gender mar avg_cc_tran_amt 1362480 50890 33 3 2 M 2 264.17 1362481 20855 36 6 2 F 2 0 1362484 10053 42 2 0 F 1 182.57 1362485 22690 25 4 0 F 1 175.40 1362486 10701 76 6 0 F 3 0 1362487 6605 71 1 0 M 2 149.16 1362488 7083 77 7 0 F 2 0 1362489 55888 35 5 2 F 3 397.07 1362492 40252 40 0 5 F 3 214.05 1362496 0 13 2 0 M 1 0