By the end of this chapter you will be able to:

• Use the switch() function
• Use the forEach() function

Additional assistance can be found in Expression Basics.

Using switch() to apply multiple tests

1. Create a new model, Attribute Scripting
2. Drag the datasource CRM onto this model.
3. From the datasource hover menu use the

   Insert excerpt
   _tableOnly _tableOnly nopanel true

    option to display all available tables.
4. Find the table SOURCE_BANK_ACCOUNT_TRANS, right-click and select create a collector.
5. Create a table from this database collector, remember to use the 

   Insert excerpt
   _table_new _table_new nopanel true

    button in the hover menu so that the table attributes are automatically configured.
6. Add an attribute to this table to hold a description of the transaction type:
   1. Name: TransactionTypeDesc

   2. Expression: work out the transaction type description from TRANSACTION_TYPE, using a switch() statement, according to the rules below – Search the help and use the examples in the help for switch() to get started.

      1. Type, switch(, and the inline help will appear. At the bottom of this popup is a link to its help page.

      2. 
         

         TRANSACTION_TYPE	Description	Example Logic
         1	"Direct Debit"	The logic being constructed will be, where the attribute TRANSACTION_TYPE is 1 set the value for the description to be "Direct Debit".
         2	"Standing Order"
         3	"ePayment"
         4	"Cheque"
         Any other value	"Unknown"	This will be the last value with no specified condition. If the value held in the attribute TRANSATION_TYPE is not cover in the logic above this will be the value set.

         
         

   3. Run the table and check that your translation of transaction type to transaction type description is correct.

Using forEach() to handle lists

1. Create an aggregate table, with name Bank Account Summary
2. Add a pipe from the table you created in the previous exercise (this should be called SOURCE_BANK_ACCOUNT_TRANS) to Bank Account Summary
3. Add to the pipe the grouping:
   1. ACCOUNT_NUM
4. Drag the attribute ACCOUNT_NUM into Bank Account Summary from either the pipe or the input table.
5. Add an attribute:
   1. Name: TotalPosTrans
   2. Type: Float
   3. Expression: using a forEach() loop loop, add up the total of all the positive transactions, and store it in this attribute.
6. Run Bank Account Summary to test this new attribute is populated

7. 
   

   Expand

   do(     // in.PAYMENT_AMOUNT contains a value for each record in the group         // Each value is passed in one by one using $amount      forEach( $amount, in.PAYMENT_AMOUNT,                 // Check if the value is positive                 if($amount > 0,             //Add the $amount to the $totalPositive  for each  value in in.PAYMENT_AMOUNT               $totalPositive = $totalPositive + $amount         )     ),     $totalPositive  )

   
   

8. When this is working, update TotalPosTrans to also calculate the total of negative transactions, and the total of all transactions – and hold them in local variables ($variables).
   1. See Using Variables for additional assistance. 
9. Add two further attributes – TotalNegTrans and TotalTrans - use these to store the values (in your local variables) of all negative transactions and all transactions that you calculated in TotalPosTrans
10. Run Bank Account Summary again to test the new attributes.