14 Attribute Scripting

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  Table 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  Create New Table 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, 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.
  7. 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. 
  8. 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
  9. Run Bank Account Summary again to test the new attributes.