2 Replies Latest reply: Jul 24, 2016 6:26 PM by Sunny Talwar RSS

    Compare 2 tables field values

    Raja Bpl

      Hi All,
      Need help to create below resultant table.


      I have 2 tables of same structure, Table FT & ST

      FT:

      Category

      Product

      Value

      A

      P1

      100

      B

      P2

      200

      C

      P3

      150

      D

      p3

      150

       

      ST:

      Category

      Product

      Value

      A

      P1

      100

      B

      P4

      450

      C

      p3

      150

       

      I need 3rd Resultant table where I need records that exist in ST but not in FT based on Category & Product field value match.

      Resultant:

      Category

      Product

      Value

      B

      P4

      450

      C

      p3

      150

       

      Sample excel worksheet is also attached.

       

      Thanks in advance.

       

      Raja

        • Re: Compare 2 tables field values
          Gysbert Wassenaar

          Try this:

           

          FT:

          LOAD

               Category,

               Product,

               Value,

               AutoNumberHash128(Category, Product, Value) as Key

          FROM

               FT_Source

               ;

           

          ST:

          LOAD

               Category,

               Product,

               Value

          FROM

               ST_Source

               ;


          Resultant:

          LOAD

               Category,

               Product,

               Value

          RESIDENT

               FT_Source

          WHERE

               NOT Exist(Key, AutoNumberHash128(Category, Product, Value) )

               ;

          • Re: Compare 2 tables field values
            Sunny Talwar

            Or may be this:


            FT:

            LOAD *,

              AutoNumber(Category&Product) as Key

            INLINE [

                Category, Product, Value

                A, P1, 100

                B, P2, 200

                C, P3, 150

                D, p3, 150

            ];

             

            ST:

            LOAD * INLINE [

                Category, Product, Value

                A, P1, 100

                B, P4, 450

                C, p3, 150

            ];

             

            Resultant:

            NoConcatenate

            LOAD Category,

                Product,

                Value

            RESIDENT ST

            WHERE NOT Exists(Key, AutoNumber(Category&Product));

             

            DROP Table FT, ST;


            Capture.PNG