Need an ASSIST with LOOKUPN

[nugget3825]

I have a transaction file which contains the fields: TYPE, PERIOD, and TOTAL

I have a TAX TABLE that contains the fields: TYPE, PERIOD, and MAX

TYPE & PERIOD are both character fields and both have the same lengths.

The TOTAL field in the transaction file is defined as N 11.2, and
the MAX field in the TAX file is defined as N 11.2 as well

Index built over the LOOKUP file is: TYPPERAMT (type, period, and amount)

Can I do the lookup with an expression such as TYPE+PERIOD+TOTAL to return certain numeric values from the TAX table ?


I have already tried:

LOOKUPN("C",TYPE+PERIOD+str(TOTAL,11,2),TYPE+PERIOD+str(MAX,11,2),"TAX.DBF","TYPPERAMT")

without success


Any help, AS ALWAYS, will be greatly appreciated.

Thanks,

lar

[Al Buchholz]

Re: Need an ASSIST with LOOKUPN

Larry

What are you trying to accomplish?

By including the amount fields in the linkage, I don't see you'll find a match unless there is only one transaction in the transaction table.

Or you can define a set that links the type and period but not the amount - rather than using the lookupn

[Stan Mathews]

Re: Need an ASSIST with LOOKUPN

AS ALWAYS a sample table is helpful. sounds like you have a stepped tax table where amounts up to a certain value are taxed at one rate, up to the next amount at another rate, etc? As Al indicates, it doesn't seem that lookup() will find anything except exact matches to the upper limit amount. You probably need to write a custom function to filter for the correct type and period and step through those records until the amount is greater than the max. Then return the rate prior to that record.

Lookupn() requires five parameters while you only show four. I believe you left out the name of the field that lookupn() should return if it had found a match..

[G Gabriel]

Re: Need an ASSIST with LOOKUPN

Index built over the LOOKUP file is: TYPPERAMT (type, period, and amount)

Can I do the lookup with an expression such as TYPE+PERIOD+TOTAL to return certain numeric values from the TAX table ?

LOOKUPN("C",TYPE+PERIOD+str(TOTAL,11,2),TYPE+PERIOD+str(MAX,11,2),"TAX.DBF","TYPPERAMT")

These two do not match. You have to use an expression identical to the index.
See example:

dim type as c
dim period as c
dim total as n
type="XYZ"
period="ABC"
total=1234567.89
?type+period+total
= "XYZABC1234567.89"

?type+period+str(total,11,2)
= "XYZABC 1234567.89"

You see he space in the second expression outcome?
Furthermore, what's the length of these character fields?
It is possible that the index will take the preceding spaces from the numeric field, but I would build it the same way as the expression.