By combining the lookup function with a GROUP BY, a column may be grouped into bins or ranges - e.g. for a price-range grouping - instead of distinct individual values. Syntax:

    lookup(key, lookupList[, resultList])

The key argument is a value to look up. It is searched for in the lookupList argument, which is a multi-value field (e.g. a strlst or varint), where each value denotes the lower (inclusive) limit for a bin. The values must be in ascending order (by key type). The largest ith value in lookupList such that key >= lookupList[i] is returned - i.e. the value for the bin that key would belong in. If key is less than all lookupList values, the first value is returned. If the optional multi-value resultList argument is given, the corresponding value from that argument is returned instead; this allows "renaming" the bins that are returned. If key is a multi-value type such as strlst or varint instead of a single value, multiple values will be returned instead of one value: one for each corresponding value of key.

By using the lookup function in a GROUP BY, a column may be grouped into ranges. For example, given a table Products with SKUs and prices such as:

    SKU    Price
    1234   12.95
    1235    5.99
    1236   69.88
    1237   39.99
    1238   29.99
    1239   25.00
    1240   50.00
    1241   -2.00
    1242  499.95
    1243   19.95
    1244    9.99
    1245  125.00

they may be grouped into price ranges (with most-products first) with this SQL (note that the dollar signs must be escaped if Vortex is used):

  SELECT   lookup(Price, convert('0,25,50,', 'strlst', 'lastchar'),
       convert('Under $25,$25-49.99,$50 and up,', 'strlst', 'lastchar'))
         PriceRange, count(SKU) NumberOfProducts
  FROM Products
  GROUP BY lookup(Price, convert('0,25,50,', 'strlst', 'lastchar'),
       convert('Under $25,$25-49.99,$50 and up,', 'strlst', 'lastchar'))

which would give these results:

  PriceRange NumberOfProducts
Under $25               5
$50 and up              4
$25-49.99               3

The lookup function was added in Texis version 7.01.1386980000 20131213.

Copyright © Thunderstone Software     Last updated: Tue Feb 21 13:46:30 EST 2017
Copyright © 2017 Thunderstone Software LLC. All rights reserved.