Lookup Next Higher/Lower value

[gaby_h]

In a lookup the options are First/Last/Closest. However I want A5 to return the next higher value. For expl if the value being looked up is 510 and Source table goes by 50 (450, 500, 550, 600,...), I want A5 to return 550. Is there a way to twist the Lookup() function to return the next higher or lower value?
Thanks

[Stan Mathews]

Re: Lookup Next Higher/Lower value

No. Use a different value to lookup.


Instead of whatever holds the "510", say a variable "someval"

lookup

if(mod(someval,50)>0,(int(someval/50)*50)+50,someval)

and use first for the higher value (than 510) to be returned.

[gaby_h]

Re: Lookup Next Higher/Lower value

Hi Stan:
Thanks for the reply.
What do I do in cases where the values don't go up by a fixed amount, like in postal rate tables, tax tables?

[Stan Mathews]

Re: Lookup Next Higher/Lower value

For returning the next higher value the lookupn() function should be what you want. It uses an index and when you specify closest it finds the next closest value after it passes the supplied value.

Given a table "arates" (index named "wt" created on lbs field)

LBS	RATE
1.50	7.10
2.90	8.40
3.60	9.10
4.10	12.60
4.70	15.00

? lookupn("C",4.111,"rate","arates","Wt")
= 15

[Tom Cone Jr]

Re: Lookup Next Higher/Lower value

Gaby, this looks like something that could be handled using a User Defined Function which you would roll yourself. i.e. it's time to create your own lookup() function.

[G Gabriel]

Re: Lookup Next Higher/Lower value

Next Higher value:
Lookup(Table,round_up(your_number/5000,2)*5000,expression)
Next Lower value:
Lookup(Table,round_down(your_number/5000,2)*5000,expression)

[G Gabriel]

Re: Lookup Next Higher/Lower value

What do I do in cases where the values don't go up by a fixed amount, like in postal rate tables, tax tables?

Simple:
For Next Higher value:
lookup(table,">=your_number", exp)

For Next Lower value:
lookup(table,"<=your_number", exp)

These should work whether the incremental value is fixed or not which makes the suggestion I gave in the previous post look stupid. I could have deleted that previous post and spared myself the embarrasment, but I decided to leave it for research purposes in case someone ponders how evolution takes place.

[Tom Cone Jr]

Re: Lookup Next Higher/Lower value

G, I agree with your next higher... but wonder whether you've actually tested the next lower approach... I'm not sure that will work.

[G Gabriel]

Re: Lookup Next Higher/Lower value

True.
Good thing I kept the previous post.

[Tom Cone Jr]

Re: Lookup Next Higher/Lower value

Here's a more general solution, relying on a custom UDF to do the heavy lifting.

-- tom

[G Gabriel]

Re: Lookup Next Higher/Lower value

Here is a UDF for the next lower value:

FUNCTION Lookup_Next_Lower AS N (tablename as c, filter as c, result_expression as c)
	x=table.external_record_content_get(tablename,result_expression,result_expression,filter)
	Lookup_Next_Lower=val(word(x,-1,crlf()))
END FUNCTION

[gaby_h]

Re: Lookup Next Higher/Lower value

Tom, your solution looks great but I tried a number of things and can't get passed the expression builder, keep getting <invalid or incomplete expression>. Could you please show how the expression should look in the expression builder with the following example:

Source table: p_rates
field: Weight (index: Bywt)
field: Postage

Current table: Transactions
field: Quantity (index: Qty)
field: Cost ---> is a calc field = LK_Next(......... , ..........., ........ , ......... , ........)
Thanks

[Tom Cone Jr]

Re: Lookup Next Higher/Lower value

LK_next("p_rates",1.45,"ByWt","H","Postage")
= 0.59

"p_rates" is the source table name, in quotes

1.45 is the numeric weight, no quotes

"ByWt" is the name of index based on the Weight field in source table, must be in quotes

"H" means next "H"igher, in quotes

"Postage" is the name of the source table field containing the numeric value to be returned, quotes


In this example the source table "P_rates" is searched for a record with weight equal to 1.45. It returns the postage value from the record containing the next higher weight. Or it returns zero if there is no higher weight in the table. In my sample the "next higher weight" is 2.0. The "postage" for weight of 2.0 is 0.59.

[gaby_h]

Re: Lookup Next Higher/Lower value

I see, so the value to be looked up (1.45) has to be hard coded in the expression, I was using the field's name (Quantity) instead of a value.
I thought A5 would take whatever value entered in a transaction table in Quantity field (1.45), goes to Source table looks up the Next Lower/Higher value in Weight field, grabs the rate (0.59) in Postage and inserts it in Cost field in Target table.

[Tom Cone Jr]

Re: Lookup Next Higher/Lower value

Perhaps the UDF needs to be tweaked. I'll be happy to check it. Post your database and point me to the calc field rule you're trying to define.

Later...

No tweaking required. Works as advertised. The value to be looked up does NOT have to be hard coded. Check the calc field rule expression for the "t_postage" field in the new "transactions" table I've built.

===================================================================
Source table: p_rates
field: Weight (index: Bywt)
field: Postage

Current table: Transactions
field: Quantity (index: Qty)
field: Cost ---> is a calc field = LK_Next("p_rates",Quantity,"ByWt","H","Postage")


-- tom