Array Queries
Array Queries |
|
Tutorial Index Rules & Decision Tables Column Queries Arrays Tables & Pattern Matching Lists VBA Multiple Rule Sets Array Queries
This example introduces some more complexity to the modular rule sets for investors. In addition to giving a yes/no decision on resale; the rules will also compute a price adjustment the investor might require for a given loan.
The required output is now a new table of resaleable loans with columns for loan ids, price adjustment, and investor, as shown in the screen shot.
loans_09_array_queries.xls
Each investor rule set already had a property Resell[?id] that provides the "yes"/"no" decision. The new investor rule sets will add a new property RateAdjust[?id] which will have the adjustment for that vendor.
Note that the key to the modular rule set architecture is that each investor rule set has some properties which are intended to be called from the main dispatch rule set. These have the same names in each investor rule set. Now the main rule set can have as a goal: ?investor : RateAdjust[?id].
The level of complexity of rate adjustment is different for the two vendors. For Acme, it is always 0.75.
For Ace, the adjustment rules are:
Note that the rules are cumulative. Array rules let us model the factors:
Adjust[Occupancy, ?id] = 1 WHEN loans[?id, 'Occupancy'] <> "Owner" Adjust[FICO, ?id] = 0.75 WHEN loans[?id, 'FICO'] < 730 Adjust[?, ?] = 0
This way, the different factors can be referenced independently, and any factor can have as many rules or as much complexity as is necessary. The last rule has ? without any text. These are variables for which a value is unimportant. The last rule says: if you get to this rule (no other rules applied), then for any factor and any loan id, the value is 0.
The advantage of writing the rules this way is that a general purpose collection rule can be used to gather up the factors and add them together. More and different factors can be added, but the general purpose rule will still work.
RateAdjust[?id] = SUM( COLLECT( ?adj,
?factor = INDEX( Adjust, 1),
?adj = Adjust[?factor, ?id] ) )
The INDEX() function is used just as it is for data tables. In this case it picks up all the explicit first indices for Adjust, which will be Occupancy and FICO. Each factor is then used to get the adjustment, ?adj, for that factor. COLLECT() gathers up the adjustments in a list. SUM() adds the values in the list.
Next, look at the collection rule in the main rule set ResaleRules. The required output is an array with columns for loan id, adjustment and investor. This is constructed as a list of lists, where the inner lists have the three desired elements. So the first argument to COLLECT() is [?id, ?adjust, ?investor].
The output array will have one row for each loan id and each investor for that loan. This means generating the output requires looping through both the loan ids and the investors. To loop through the investors first create a list with the investor names:
Investors = ['Ace', 'Acme']
Now the FOREACH argument of COLLECT() can loop through loans and investors: ( ?id = INDEX(loans, 1) AND ?investor = MEMBER( Investors ) )
The conditions of COLLECT() are that the loan be resaleable for that investor, and if so, ?adjust is the adjustment: ( ?investor : Resell[?id] = "yes" AND ?adjust = ?investor : RateAdjust[?id] )
One last touch is that the lists be sorted, so that for loan ids with more than one investor, they are in order of smallest adjustment first. This is what the collection rule looks like:
ResaleInfo = SORT( COLLECT( [?id, ?adjust, ?investor], ( ?id = INDEX( loans, 1) AND ?investor = MEMBER( Investors ) ), ( ?investor : Resell[?id] = "yes" AND ?adjust = ?investor : RateAdjust[?id] ) ) )
Before trying to create an Excel array from the output, it is a good idea to try the query as a single cell value to see what the results look like.
Lists of lists used to fill an array
In the screen shot above, the result is a list of lists as required. And each list has three elements, as required.
The ResaleInfo property could be queried from VBA, and VBA used to format the output as described in the previous section. Or Excel array function techniques can be used.
Excel supports array functions. If a function is called from within a range of cells, and the function is entered using Ctrl-Shift-Enter, and the function returns an array, then Excel will populate the cells in the range with the values from the array.
RQuery() knows when it is called as an array function. In that
case, if the answer is a list or list of lists, it formats the lists into a
VBA array so excel can display the results in a range of cells.
That is exactly what is done in the screen shot at the beginning of this chapter, and how the above list winds up in a nicely formatted Excel table. Note in the formula window in the first screen shot the query is displayed in curly braces { }. This indicates that it is an array query.
|
Copyright ©2005-7 Amzi! inc. All Rights Reserved.
|