Approximate Lookup

[Jon von der Heyden]

Hello

I'm struggling to think of a 'not so complicated' way of achieving similar to an approximate VLOOKUP.

I have a large table, and one of its' many fields is 'Days Overdue', which can be any integer greater than zero. I would like to update another existing field called 'Aged Bucket' to categorise each record according to 'Days Overdue'.

In excel I am lucky enough to be able to perform an approximate lookup, e.g:

=LOOKUP(F4,{0,"0-30 Days";31,"31-60 Days";61,"61-90 Days"})

Where F4 houses the 'Days Overdue'. E.g: If 'Days Overdue' = 34 then it should return "31-60 Days".

I would like to do similar but I would like to store the age categories in a table. I would like to avoid a big hiddeous expression if possible.

Suggestions gratefully received! Thanks.

 

[RoryA]

If they are regular 'buckets', you might use the Partition function in a query rather than a table?

 

[Joe4]

If it was me, I might look at creating a User Defined Function to do this, especially using CASE SELECT statements. I find those are much easier to read and maintain than some really long, big query expression, and if you re-use it in other queries, you only need to update it in one place.

 

[Jon von der Heyden]

Rory, unfortunately the buckets are not all at regular intervals and I need to return specific strings. But thanks alot anyway because that is a handy function to know of.

Joe4, thanks that is probably the way I will have to go.

Cheers

 

[RoryA]

OK, the usual other way to do it is with a subquery - see example(s) here.

 

[Jon von der Heyden]

I created a function to loop through a lookup table and return the corresponding bucket according to the Days Overdue. Works a treat and users can simply manage the table if they want to change bucket values.

Thanks

 

[Jon von der Heyden]

OK, the usual other way to do it is with a subquery - see example(s) here.

Neat! Thank you

 

[Joe4]

Forgot about that method.
Don't have the need to use it much myself (seldom do approximate matches), but that is a very good way to go about it.