8 Replies Latest reply: Oct 15, 2015 10:01 AM by Crystle Stamper RSS

    Lookup - find combination

      Hi.

       

      Got a new problem that can't resolve by myself.

       

      Customer database contains 1. car info 2. fact table with car number in comment field.

      Problem: need some text parsing to create link between 1 and 2.

       

      Example:

       

      cars:

      registrationNo, group

      ABC123, Transport
      BC234, Expedition

       

      facts:

      date, amount, comment

      2015-01-01, 100, Insurance expenses for ABC 123

      2015-01-02, 200, Insurance expenses for BC234 (document no 123456)

       

      Need to assign car group for every fact row.

      Preferred result table:

      date, amount, comment, group

      2015-01-01, 100, Insurance expenses for ABC 123, Transport

      2015-01-02, 200, Insurance expenses for BC234 (document no 123456), Expedition

       

      There are additional problems that make task even worse:

      1. registration numbers in comments are entered not uniformly - sometimes they contain spaces and sometimes not

      2. registratios numbers in comments varry in length - 5 or 6 chars (if without spaces)

      3. registration numbers in comments might be in any place of comment (usually end but sometimes mid)

       

      I have gut feeling like i need some mix of lookup and match functions just can't figure out how to do it exactly.

       

      Any ideas?