Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Insert excerpt
_Banners
_Banners
nameanalysis
nopaneltrue

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
    nopaneltrue
     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
    nopaneltrue
     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 is populated

  7. Expand
    titleClick here to see an example...

    // Here is a working example for adding up all the positive values

    do(

        // in.PAYMENT_AMOUNT contains a value for each record in the group

            // Each value in a group is passed in one by one at a time using $amount 
        forEach( $amount, in.PAYMENT_AMOUNT,
           

            // Check if the value is positive  positive        

            if($amount > 0,

                //Add the $amount to the $totalPositive  for each  value in in.PAYMENT_AMOUNT  

                $totalPositive = $totalPositive + $amount

            )
        ),

         // Value to be output
        $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.