1 Reply Latest reply: Aug 28, 2018 5:45 PM by Rob Wunderlich RSS

    Looping Through Data with Multiple Wildcard Values

    Frederick Jones

      Hello everyone -


      I have a table with the fields: DBA (Doing Business As), Merchant Wildcards (which will correspond to a Merchant Name), and Merchant Codes. For example:


      DBAMerchant WildcardMerchant Code
      Bob's General StoreMerchant 1*1234
      Bob's General StoreMerchant 1*4567
      Bob's General StoreMerchant 4*1234
      Frank's Grocery StoreMerchant 2*1234
      Frank's Grocery StoreMerchant 2*4567


      I have a data set that contains the Merchant Name, Merchant Code, and many other fields at the individual transaction level. I need to load any transaction that is like one of the merchant wildcards above AND matches the corresponding merchant code.


      For example, this transaction would be loaded, and would map to Bob's General Store:


      Merchant NameMerchant Code
      Merchant 1abc1234


      These transactions would not be loaded at all:


      Merchant NameMerchant Code
      Merchant 3abc1234
      Merchant 1abc8976


      My initial thought how to do this is to use two loops:


      - loop by each DBA

            - loop by each Merchant Wildcard

            - match on any Merchant Code for that Merchant Wildcard


      Until I have looped through each combination of merchant wildcard's and merchant codes, for every DBA.


      My questions:


      1) Can you store a wildcard into a variable, which the like operator can evaluate against?

      2) Is there a like function that lets you evaluate one record against multiple wildcard values?

      3) Any ideas on how to do this more efficiently? Open to any idea here.


      Your help is appreciated.