2 Replies Latest reply: Jun 20, 2017 2:11 PM by Gareth Rogers RSS

    Script: how to filter to load only if value is in one of several ranges

    Gareth Rogers

      I have two data sources, one containing transactional data of cost centre and amount, and another parameter table listing the ranges of cost centres that I actually want to load.  Records where the cost centre is not in any of the ranges should be dropped.

       

      Transactional Data:

      Cost Centre, Amount

      1001, 25.7

      1002, 45.6

      1003, 34.6

      1004,45.1

      1005, 1.2

      1006, 33.9

      1007, 33.4

      1008, 1.8

      1009, 88.4

      1010, 5.4

       

      Cost Center Ranges to be loaded:

      Min, Max

      1002, 1004

      1007, 1009

       

      In this simple example, my loaded table would be:

      Cost Centre, Amount

      1002, 45.6

      1003, 34.6

      1004,45.1

      1007, 33.4

      1008, 1.8

      1009, 88.4

       

      All I can come up with is to convert the ranges into individual values (e.g. 1002 to 1004 becomes 1002, 1003, 1004) and do a join or where exists.  Any suggestions of an easier way to accomplish this?   The list of ranges can change so there can be no hard-coding in the script.


      Thanks,

      Gareth.