4 Replies Latest reply: Jun 9, 2015 10:20 AM by Arun Raj RSS

    How many columns match between two tables

    Arun Raj

      Hi,

       

      I have two Tables both with the same column names. I need to find out how many columns match between the two tables -

       

      Table 1:

      Name, DOB, City, Country

      Emily, 20/10/1986, London, UK

      Peter, 10/06/1992, New York, US

      Kevin, 23/08/1982, Sydney, Australia

      Rose, 08/11/1981, Paris, France

       

      Table 2:

      Name, DOB, City, Country

      Emily, 20/10/1987, London, UK

      Peter, 10/06/1992, New York, US

      Kevin, 23/10/1982, Auckland, NewZealand

      Gary, 19/01/1983, Berlin, Germany

       

       

      Resulting Table should take Table 1 as the main table :

      Table 3:

      Name, DOB, City, Country, MatchingFields

      Emily, 20/10/1986, London, UK, Matching on Name and City and Country

      Peter, 10/06/1992, New York, US, Matching on Name and DOB and City and Country

      Kevin, 23/08/1982, Sydney, Australia, Matching on Name

      Rose, 08/11/1981, Paris, France, No Matches

       

      Any Help will be highly appreciated.

       

      Thank You

       

      Arun

        • Re: How many columns match between two tables
          Sunny Talwar

          Hey Arun,

           

          Do you need this on the front end or in script also works?

           

          Best,

          Sunny

            • Re: How many columns match between two tables
              Arun Raj

              Hi Sunny,

               

              I was hoping to do it in script.

               

              Thanks

              Arun

                • Re: How many columns match between two tables
                  Sunny Talwar

                  Arun, this may not be the best way to do it, but see if this helps and can be further tweaked for your needs:

                   

                  Table1:

                  LOAD * Inline [

                  Name, DOB, City, Country

                  Emily, 20/10/1986, London, UK

                  Peter, 10/06/1992, New York, US

                  Kevin, 23/08/1982, Sydney, Australia

                  Rose, 08/11/1981, Paris, France

                  ];

                   

                  Table2:

                  LOAD Name as NameCheck,

                    DOB as DOBCheck,

                    City as CityCheck,

                    Country as CountryCheck;

                  LOAD * Inline [

                  Name, DOB, City, Country

                  Emily, 20/10/1987, London, UK

                  Peter, 10/06/1992, New York, US

                  Kevin, 23/10/1982, Auckland, NewZealand

                  Gary, 19/01/1983, Berlin, Germany

                  ];

                   

                  Table:

                  NoConcatenate

                  LOAD *

                  Resident Table1;

                   

                  Join(Table)

                  LOAD *,

                    1 as NameMatch

                  Resident Table1

                  Where Exists(NameCheck, Name);

                   

                  Join(Table)

                  LOAD *,

                    1 as DOBMatch

                  Resident Table1

                  Where Exists(DOBCheck, DOB);

                   

                  Join(Table)

                  LOAD *,

                    1 as CityMatch

                  Resident Table1

                  Where Exists(CityCheck, City);

                   

                   

                  Join(Table)

                  LOAD *,

                    1 as CountryMatch

                  Resident Table1

                  Where Exists(CountryCheck, Country);

                   

                  DROP Tables Table2, Table1;

                   

                  FinalTable:

                  LOAD *,

                    If(SubField(Trim(If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

                    'No Match', 'Match on ' &

                    If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

                    If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country'))), ' ', -1) = 'and',

                    Left(Trim(If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

                    'No Match', 'Match on ' &

                    If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

                    If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country'))),

                    Index(Trim(If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

                    'No Match', 'Match on ' &

                    If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

                    If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country'))), ' ', -1)-1),

                    If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

                    'No Match', 'Match on ' &

                    If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

                    If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country')))

                    as Match

                  Resident Table;

                   

                  DROP Table Table;


                  Output in a table box:


                  Capture.PNG


              • Re: How many columns match between two tables
                Arun Raj

                Hi Sunny,

                 

                Thanks a lot. I was trying to do this using a loop. But your method is much better and easier.

                 

                One last thing, I should have mentioned this earlier. So stupid of me.

                I have got one more field that i need to match i.e. Address and I do not need a complete match for this field. It can be a complete or partial match. Even if it is a partial match it is match.

                 

                For example

                 

                Table1:

                LOAD * Inline [

                Name, DOB, City, Country, Address

                Emily, 20/10/1986, London, UK, abcd

                Peter, 10/06/1992, New York, US, 1234

                Kevin, 23/08/1982, Sydney, Australia, wxyz

                Rose, 08/11/1981, Paris, France, 5678

                ];

                 

                Table2:

                LOAD Name as NameCheck,

                  DOB as DOBCheck,

                  City as CityCheck,

                  Country as CountryCheck,

                Address as AddressCheck;

                LOAD * Inline [

                Name, DOB, City, Country, Address

                Emily, 20/10/1987, London, UK, ab78 

                Peter, 10/06/1992, New York, US, 1234

                Kevin, 23/10/1982, Auckland, NewZealand, 90yz

                Gary, 19/01/1983, Berlin, Germany, jhg

                ];

                 

                So for

                Emily - Match on Name and City and Country and Address

                 

                The exists () function seems to do an exact match.Is there a similar function which does partial matches?