3 Replies Latest reply: Nov 13, 2017 4:58 AM by Shraddha Gajare RSS

    Camparing fields from two different tables

    Supriya R

      Hi,

       

      I have two Tables

       

      Table1:

      Load

      Street1,

      Street2,

      cellPhoneNumber,

      HomePhoneNumber,

      Pincode

      From AddressTable;

       

      Table2:

      Load

      Street1,

      Street2,

      cellPhoneNumber,

      HomePhoneNumber,

      Pincode

      From ContactTable;

       

      Here i need to compare Street1, Street2, cellPhoneNumber, HomePhoneNumber, Pincode field values in both table, if both field values are same then i need to add and update a flag column in ContactTable,  indicating 0 for 'equal' or  1 for 'not equal'  in each row. This i need to do in Script.


        • Re: Camparing fields from two different tables
          Shraddha Gajare

          Try below

           

          Table1:

          Load

          Street1,

          Street2,

          cellPhoneNumber,

          HomePhoneNumber,

          Pincode

          From AddressTable;

           

          Noconcatenate

          Qualify *;

          Table2:

          Load

          Street1,

          Street2,

          cellPhoneNumber,

          HomePhoneNumber,

          Pincode,

          '0' as Flag

          From ContactTable

          Where Exists(Street1,Street1) and Exists(Street2,Street2) and Exists(cellPhoneNumber,cellPhoneNumber) and

          Exists (HomePhoneNumber,HomePhoneNumber) and Exists (Pincode,Pincode);

           

          Concatenate (Table2)

           

          Load

           

          Street1,

          Street2,

          cellPhoneNumber,

          HomePhoneNumber,

          Pincode,

          '1' as Flag

          From ContactTable

          Where not Exists(Street1,Street1) and Not Exists(Street2,Street2) and Not Exists(cellPhoneNumber,cellPhoneNumber) and

          Not Exists (HomePhoneNumber,HomePhoneNumber) and NotExists (Pincode,Pincode);

           

          Unqualify *;

            • Re: Camparing fields from two different tables
              Supriya R

              HI Shraddha,

               

               

              AddressTable:

              LOAD * INLINE [

              Street1,Street2,cellPhoneNumber,HomePhoneNumber,Pincode

              delhi,,,,

              kolkata,maggadi,2342342342,2342342342,456345

              ,korea,45645,,

              ,,,,

              ];

               

              NoConcatenate

              ContactTable:

              LOAD * INLINE [

              Street1,Street2,cellPhoneNumber,HomePhoneNumber,Pincode

              delhi,,,,

              koata,maggadi,666666,11111,9999

              ,korea,,,

              ,,,,

              ];

               

              NoConcatenate

              Qualify *;

              Table3:

              Load

              Street1,

              Street2,

              cellPhoneNumber,

              HomePhoneNumber,

              Pincode,

              '0' as Flag

              Resident ContactTable

              Where Exists(Street1,Street1) and Exists(Street2,Street2) and Exists(cellPhoneNumber,cellPhoneNumber) and

              Exists (HomePhoneNumber,HomePhoneNumber) and Exists (Pincode,Pincode);

               

              Concatenate (Table3)

              Load

              Street1,

              Street2,

              cellPhoneNumber,

              HomePhoneNumber,

              Pincode,

              '1' as Flag

              Resident ContactTable

              Where not Exists(Street1,Street1) and Not Exists(Street2,Street2) and Not Exists(cellPhoneNumber,cellPhoneNumber) and

              Not Exists (HomePhoneNumber,HomePhoneNumber) and Not Exists (Pincode,Pincode);

               

              Unqualify *;

              drop Table ContactTable;

               

              This is not giving proper result,

               

               

              i should get flag = 1, only for 2nd and 3rd rows of table as they are changed.