4 Replies Latest reply: Oct 9, 2015 6:00 AM by Peter Süssinger RSS

    join two tables by substring

    Peter Süssinger

      Hello,

       

      I am trying to join two tables by a substring like is shown with a picture below, but I didn't have any luck with it.

      Can you please help me.

       

      substringjoin.JPG

       

      As you can see in the picture, I want to join Table2 to Table1, where Field C is primary key and a substring to Field A from Table1.

       

       

      I would appreciate some help and thank you on advance

        • Re: join two tables by substring
          Henric Cronström

          If it always is character 2,3, and 4 from FieldA that should be used, then you can create a new field

            Mid(FieldA, 2, 3) as Key

          and make your join on this key.

           

          But I suspect you want a more general solution, and if so, it could be a challenge, I would explore the option of creating a mapping table from table 2 and use mapsubstring() instead of a join.

           

          HIC

          • Re: join two tables by substring
            Nagaian Krishnamoorthy

            You may use the following script and the table "Final" has the desired result.

             

            Table1:

            Load * Inline [

            FieldA, FieldB

            abcde,123

            fghjk,456

            lmnop,789

            qrstu,111

            ];

             

            Table2:

            LOAD * Inline [

            FieldC, FieldD

            rst,222

            mno,333

            ghj,444

            bcd,555

            ];

             

            Outer Join (Table1) LOAD * Resident Table2;

            Drop Table Table2;

             

            Final:

            NoConcatenate

            LOAD FieldA, FieldB, FieldC, FieldD Resident Table1

            where Index(FieldA, FieldC) > 0;

            Drop Table Table1;

            • Re: join two tables by substring
              Tresesco B

              I have worked out something. , yeah, it's a bit tricky.

               

              Table1:

              Load * Inline [

              FieldA, FieldB

              abcde, 123

              fghik, 456

              lmnop, 789

              qrstu, 111

              ];

               

               

              Table2:

              Load * Inline [

              FieldC, FieldD

              rst, 222

              mno, 333

              ghi, 444

              bcd, 555

              ];

               

               

              Map:

              Mapping Load

                FieldC,

                '_'&FieldC&'_'&FieldD as New

              Resident Table2;

               

               

              Final:

              Load

                SubField(MapSubString('Map', FieldA), '_',2) as FieldC,

                FieldA,

                FieldB

              Resident Table1;

              Join

              Load

                FieldC,

                FieldD

              Resident Table2;

               

              Drop table Table1, Table2;


              Note: Under-score ('_') as been taken to separate out the sub strings assuming there would not be any '_' in your key fields.