Lists and COLLECT()

Tutorial Index     Rules & Decision Tables     Column Queries     Arrays     Tables & Pattern Matching     Lists     VBA     Multiple Rule Sets     Array Queries

loans_06_lists.xls

Rather than adding columns to the worksheet input data, the application might require separate reporting of all the loans a particular investor can accept. For Ace, this would be loans 101, 104, 105, 108, 110 and 112.

Lists are represented in ARulesXL with square brackets, so the resaleable loans for Ace would be: [101, 104, 105, 108, 110, 112].

Lists are generated in ARulesXL using the COLLECT statement. Here is our application requirement:

For a given investor, find all the loan ids of loans that can be resold to that investor.

And here is how it is expressed in ARulesXL using COLLECT to loop through all the loans looking for the resaleable ones:

Resaleable[ ?investor ] = COLLECT ?id FOREACH ?id = INDEX( loans, 1 ) WHERE Resell[?investor, ?id] = yes

For Resaleable['Ace'], this will create the list [101, 104, 105, 108, 110, 112] as shown in the screen shot. COLLECT and the INDEX() function are explained in more detail below.

loans_06_lists.xls

The syntax of COLLECT is:

COLLECT item(s) FOREACH loop variable(s) WHERE condition(s)

• item(s) - The variable(s) output from each pass through the loop. Multiple output variables can be put in a list [?x, ?y], so the output will be a list of lists.
• loop variable(s) - The variables that control the loop and how they are generated. Multiple generator phrases can be separated by AND and enclosed in parenthesis.
• condition(s) - The conditions and other goals used to set the output variable values. Multiple conditions can be enclosed in parenthesis and contain any of the syntax allowed in rule conditions.

COLLECT can also be entered as a function with three arguments:

COLLECT( item(s), loop variable(s), condition(s) )

This is useful when other ARulesXL list processing functions are to be applied to the list. For example, the SORT() function might used to sort the list output from COLLECT().

The values of the loop variables can be set in a variety of ways. In this case the values are the row indices of the input table loans. The INDEX() function can be used to generate all of the index values from a table. The syntax is:

INDEX( table, index )

• table - The table name
• index - The index. For two-dimensional tables 1 is the row index and 2 is the column index. Tables can have more than two dimensions.

So in the example INDEX( loans, 1 ) will generate the values 101, 102, 103, ..., 115. If column indices were needed, INDEX( loans, 2 ) would generate the values AMOUNT, FICO, LTV, Occupancy, and Rate.

Copyright ©2005-7 Amzi! inc. All Rights Reserved. ARulesXL and Amzi! are trademarks or registered trademarks of Amzi! Microsoft and Excel are trademarks or registered trademarks of Microsoft Corporation.