21 Replies Latest reply: May 16, 2014 6:12 AM by Dariusz Mielczarek RSS

    Intervalmatch on several fields with AND operation?

    Ola Amphan

      I am trying to figure out how to categorize a transaction depending on whether several different fields in the transaction are within a certain range. In my current task I have seven fields, let's call them ACCODE01 - 07 and seven corresponding ranges, ACCODE01_START, ACCODE01_END, ACCODE02_START, ACCODE02_END etc. If each ACCODEXX field is within its corresponding range, ie. ACCODE01 is in ACCODE01_START to ACCODE01_END AND ACCODE02 is in ACCODE02_START to ACCODE02_END AND etc. then I want to mark the transaction with a Category.

       

      Let's say I have these tables (random data, might not add up), what I want is to add CATEGORY from INTERVAL: to FACT: where ACCODEXX is in THE ACCODEXX_START to ACCODEXX_END range. If there is no match, no category should be assigned.

      FACT:
      LOAD * INLINE [
          ACCODE01, ACCODE02, ACCODE03, ACCODE04, ACCODE05, ACCODE06, ACCODE07, VERID, AMOUNT
          10000, 40261, 12340, 12300, 5478, 2541, 2587, 123456789, $100
          20000, 86210, 43210, 45600, 9866, 2365, 1236, 987546, $1000
          30000, 86400, 58740, 78900, 2222, 5895, 9854, 124578, $200
          40000, 86111, 98560, 98700, 9874, 4578, 7854, 326598, $1100
          50000, 46435, 12450, 65400, 2587, 1245, 2569, 785612, $800
          60000, 45698, 45780, 32100, 3698, 6542, 3254, 324578, $500
          70000, 32145, 65540, 74100, 1478, 1111, 1256, 1245782, $600
          80000, 78954, 32120, 36900, 2589, 2224, 9854, 69854225, $1900
      ];
      
      INTERVAL:
      LOAD * INLINE [
          CATEGORY, ACCODE01_START, ACCODE01_END, ACCODE02_START, ACCODE02_END, ACCODE03_START, ACCODE03_END, ACCODE04_START, ACCODE04_END, ACCODE05_START, ACCODE05_END, ACCODE06_START, ACCODE06_END, ACCODE07_START, ACCODE07_END
          APPLES, 0000, 8999, 40260, 40270, 00000, 96999, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 0000
          ORANGES, 0000, 8999, 65410, 65510, 00000, 96999, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 0000
          BANANAS, 0000, 8999, 86110, 86710, 03560, 03560, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 9999
          CHERRYS, 0000, 8999, 86310, 86330, 03560, 03560, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 9999
          LIMES, 0000, 8999, 86400, 86400, 03560, 03560, 0000, 9999, 0000, 9999, 0000, 9999, 0000, 9999
          LEMONS, 0000, 8999, 46430, 46440, 00000, 96999, 1000, 1099, 0000, 9999, 0000, 9999, 0000, 0000
      ];
      
      

       

      I'd really appreciate any help to move forward on this task.

       

      Thanks in advance!

       

      Ola