Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hey Arun,
Do you need this on the front end or in script also works?
Best,
Sunny
Hi Sunny,
I was hoping to do it in script.
Thanks
Arun
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:
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?