Function Names
table lookup smart, table lookup smart ignore caseDescription
This function works very similarly like table lookup top down(), with the only difference that the lookup table contains simplified
B4P comparison expressions instead of hard values in the search pattern columns. This feature provides a lot of flexibility because
you can make a much more flexible description of the look-up table using some rules (e.g. comparison operators, ranges and selections).
A match is fulfilled for a particular row if the comparisons made in all search pattern patterns returned 'true'.
Examples to be specified in the search columns in the lookup table (Syntax is same as in B4P expressions):
| Building | Equals to "Building" |
| =Building | Equals to "Building" |
| <>Building | Must not equal to "Building" |
| B* | Example of wildcard symbols: Begins with B |
| 1..10,20 | True if between 1 and 10, or equal to 20. |
| '3..5' | Equals to "3..5" (Text string) |
| >10 | Greater than 10 |
| Dog,*at,Fly | Commas allow for flexible seletion OK for Dog, Cat, Rat, Bat, Fly, Gnat, ... |
| Dog,?at,Fly | Commas allow for flexible seletion OK for Dog, Cat, Rat, Bat, Fly, but not Gnat (? is placeholder for 1 character) |
| Don't care | |
?* | Must contain at least 1 characters, contents don't care. |
Examples: You have a product portfolio and provide some technical data in the reference table. The table can be used to identify a suitable
product based on your specification. Other example: Describe a bunch of restaurants with table locations, recommended offerings, party
size (number of seats per table), price range for main course, etc., and you can search for a restaurant by preferences.
Beginning with release 8.03, B4P expressions returning Boolean values can be specified as alternatively. The contents are recognized as B4P
expressions if they start with a colon (:). In the expression, the context for partial table references is provided, referring to the current
row and column in the target table being compared. Example: :even([.]) considers value as found if the value in the target table
contains an even number. In these expressions, other columns in the target table can be referenced by specifying the corresponding column name or
number.
Examples to be specified in the search columns in the lookup table (Syntax is same as in B4P expressions):
| :[.]=Building | Equals to "Building" |
| :[.]{0}=B | Begins with B (comparing the 1st character in the string) |
| :[.]{}=4 | Contents must consist of 4 characters |
| :[.]=[Town] | true if the contents in the current column equals to the contents in column named "Town" in the target table |
| :odd([.]) | true if contents contain odd numbers |
Note: The 'ignore case' option in the function name does not influence execution of B4P expressions explained above. In order to ignore
cases, use the '+' modifier in the comparison, e.g. [.]=+'hello'.
The search time ratio versus table size n is O ( n) because every search is a top-down search.
Hint: If you create the lookup table, start with the most specific selections. Further down, open up more (using wildcard symbols).
Consider default information (in casse of no matches in the rows above) in the last row: Put '*' in all search pattern columns and write "All others" or "Not found" in the output column.
with this approach, you will capture all searches.
Call as: procedure or function
Restrictions
Indirect parameter passing is disabled
Parameter count
6 - 8 (For parameter description and return values: see table lookup() )
Examples
table load( lookup, "Examples/Table Lookup Example 2.csv" );
table initialize( target,
{ { Preference, Seats Needed, Location },
{ vegi pizza, 6, inside },
{ pizza tonno, 5, outside },
{ hamburger, 2, inside },
{ beer, 1, bar },
{ fries, 2, "self-service" },
{ pasta napoli, 8, inside },
{ steak, 10, inside },
{ steak, 3, outside } } );
count[] = table lookup smart ignore case
( target, {Seats Needed, Preference,Location}, { Place to go, Phone, Row },
lookup, {Table size, Offering, Ambience}, { Restaurant Name, Phone Number, '#row' }, true, Counter );
echo("Lookup table:");
table list( lookup );
echo("Target table after lookup (", count[], " items found):");
table list( target );
Output
Lookup table:
0 : Offering | Table size | Ambience | Restaurant Name | Phone Number | Counter
1 : *Chicken* | 2,4,6 | inside,bar | Rôtisserie de Paris | (212) 555-1234 |
2 : *Curry* | 1..10 | "self-service" | Bharat take-away | (212) 555-2345 |
3 : *Pizza*,*Pasta* | 4,6,8 | inside,outside | Luigis | (212) 555-3456 | 2
4 : *Beer*,*Wine* | * | bar | Luigis | (212) 555-3456 | 1
5 : *Pizza*,*sea*food* | * | inside,outside | All-Italian Trattoria | (212) 555-4567 | 1
6 : *Fish*,*sea*food* | 2..8 | outside | Wharf Place | (212) 555-5678 |
7 : *Burger*,Fries | 1..20 | =inside,"self-service" | Mr. Burgermeister | (212) 555-6789 | 2
8 : * | 1..6 | * | Mom cooks for you | Mom's number | 1
9 : * | * | * | Not found | -- | 1
Target table after lookup (8 items found):
0 : Preference | Seats Needed | Location | Place to go | Phone | Row
1 : vegi pizza | 6 | inside | Luigis | (212) 555-3456 | 3
2 : pizza tonno | 5 | outside | All-Italian Trattoria | (212) 555-4567 | 5
3 : hamburger | 2 | inside | Mr. Burgermeister | (212) 555-6789 | 7
4 : beer | 1 | bar | Luigis | (212) 555-3456 | 4
5 : fries | 2 | self-service | Mr. Burgermeister | (212) 555-6789 | 7
6 : pasta napoli | 8 | inside | Luigis | (212) 555-3456 | 3
7 : steak | 10 | inside | Not found | -- | 9
8 : steak | 3 | outside | Mom cooks for you | Mom's number | 8
See also
table lookup
table lookup smart once
table lookuop with rules
table integrate smart
table expand smart
table digest smart