Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Equivalent of Extended Lookup in Table
Go
New
Search
Notify
Tools
Reply
  
Equivalent of Extended Lookup in Table
 Login/Join
 
Member
posted
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, 2005Reply With QuoteReport This Post
Virtuoso
posted 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, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Thanks Jack, worked like a charm.
 
Posts: 3 | Location: New York | Registered: January 26, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Equivalent of Extended Lookup in Table

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.