 Focal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Equivalent of Extended Lookup in Table
 Go New Search Notify Tools Reply  Equivalent of Extended Lookup in Table Member posted January 26, 2005 01:27 PM
I would like to find a technique that would give the equivalent of doing a LOOKUP(field GE)in a table request:

Here is the basic scenario, why I need this

I have a table with the 50 States, for each state
we want to "lookup" mortgage rates and points and maybe other fields based on the State and the lending price desired.

Here is the typical scenario, (I could not display the table here because I can't paste it in here and have it look decent, so I am describing it in words instead)-
If the state is Arizoa and the selling price is from .01 to 10,000 then the Mortgage rate is .1 and the points are 1.

If the state is Arizona and the selling prices from 10,000.01 to 20,000 then Mortgage rate is .15
and the points are 2.
etc. for Arizona

If the state is Colorado and the Lending Amount is from 8,000 to 26,000 then the mortgage rate is .2 and the points are 2.5
If the state is Colorado and the Lending Amount is from 26,000.01 to 40,000 then the mortgage rate is .3 and the points are 1.9.
and so on for Colorado and other states.

The lending amount ranges are haphazard and follow no particular pattern, nor do the rates or points.
If I were using Modify I could create a table and do a LOOKUP(field GE) and get the desired Rate and points, where the field would be the concatenation of the State and the particular lending amount.

But I am not using Modify, I have to use Table.

I could use a bunch of If then Else statements but the number would so large it would be prohibitive. I also have to do this "Lookup" in a bunch of lines of code and don't want to repeat the exhaustive if then else logic over and over again.

Does anyone have any technique that would help to alleviate this problem? I thought of decode from a file, but I believe that only works if you are looking for an exact match.

Thank You.

 Posts: 3 | Location: New York | Registered: January 26, 2005   IP
Virtuoso posted January 26, 2005 02:21 PM Hide Post
Use a one-to-many join on State, with a WHERE clause in the TABLE request to yield a unique match.

JOIN state in file1 to ALL state in file2
...
where file1.amount ge file2.lower_bound
and file1.amount le file2.upper_bound;
...

(Note that, although you know that the condition ensures a unique match, Focus regards this as a nonunique join. That can result in "multiple path" messages.)

 Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005   IP
Member posted January 28, 2005 04:02 PM Hide Post
Thanks Jack, worked like a charm.

 Posts: 3 | Location: New York | Registered: January 26, 2005   IP
 Reply  Focal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Equivalent of Extended Lookup in Table