1 Reply Latest reply: Aug 6, 2012 7:09 AM by jagan mohan rao appala RSS

    How to resolve using join/merging tables.

      Hi,

       

      I have two tables,

       

      Table 1 - this table dont not complete records

      Table 2 - this table have complete records

       

      I have to join the two tables connecting w/ same key which is ITEM CODE, but I don't wan't to join items if item is already existed in Table 1

       

      See example below

       

      Table 1

           ITEM CODE     BRAND

           ITEM0001        ACER

           ITEM0002        MSI

       

      Table 2

          ITEM CODE     BRAND

           ITEM0003         SAMSUNG

            ITEM0004         ASUS

            ITEM0001         ACERS

            ITEM0002         MSIS

       

      Notice that ITEM0001 and ITEM0002 existed in both tables, so the output should be

       

      Full Table

          ITEM CODE     BRAND

           ITEM0001        ACER - this time the brand of Table 1 will be follow

           ITEM0002        MSI - this time the brand of Table 1 will be follow

            ITEM0003        SAMSUNG - from Table2

            ITEM0004        ASUS - from Table2

       

       

      Any idea? Thanks in advance.

        • Re: How to resolve using join/merging tables.
          jagan mohan rao appala

          Hi,

           

          Try this script

           

          Table1:

          LOAD * INLINE [

           

               ITEMCODE,     Table1BRAND

           

               ITEM0001,        ACER

           

               ITEM0002,        MSI];

           

           

           

          OUTER JOIN(Table1)

          LOAD * INLINE [

           

              ITEMCODE,     Table2BRAND

           

               ITEM0003,         SAMSUNG

           

                ITEM0004,         ASUS

           

                ITEM0001,         ACERS

           

                ITEM0002,         MSIS];

           

          Table2:

          LOAD

               ITEMCODE,

               IF(Len(Table1BRAND) > 0 AND NOT IsNull(Table1BRAND), Table1BRAND, Table2BRAND) AS Brand

          RESIDENT Table1;

           

          DROP TABLES Table1;

           

           

          Hope this helps you.

           

          Regards,

          Jagan.