Compare the content of two tables

[vonpape]

Hello Together,

I would like to compare two tables.Table 1 is the mastertable (10.000 Records). Table 2 has 1500 records.
Now I want to know if some of the 1500 records from Table 2 exist in table1.
Unfortunately there is no real key-field, the key is very softly like
table2.name exists in table1.company_name1
or
table2.name exists in table1.company_name2
or
table2.adress exists in table1.city
or
table2.adress exists in table1street

Is it possible to do this in the script somehow?

Thank you very much!

enclosed the sample Tables:

Table1:

LOAD * INLINE [
Company_name1, Company_name2, street, city
ABC, Company, Dorfstrasse 5, Hamburg
Test, Aktiengesellschaft, J.-F.Street, Washington
Toys World, GmbH&Co, Musterweg, Munich
];

Table2:

LOAD * INLINE [
Name, Adress
Microsoft AG, Redmond
AaBbCc, Dorfstrasse 5 Hamburg
Toys Word GmbH, München
Test Aktiengesellschaft, New York
];

Result:
Test Aktiengesellschaft (Table2.Name exist in Table1.company_name1)
AaBbCc, Dorfstrasse 5 Hamburg, (Table2.Adress exist in Table1.street)
Toys Word GmbH (Table2.Name exist in Table1.Company_name1)

[Miguel_Angel_Baeyens]

Hello,

You can use the exists() function to check that

LOAD ... IF(EXISTS(Company_Name1, Name), Name) AS Company_Exists, ...

that will check for every record whether "Name" value exists in the Company_Name1 field, and will return value of "Name" in a new field called "Company_Exists"

Hope that helps

[vonpape]

Hello Miguel,

thanks for your help. It is possible to work with wildcards? My Idea is to work with a concatenated field (Company_name1&Company_name2&street&city) as key,

Table1_new:

Load *,

Company_name1&Company_name2&street&city as key

resident Table1;

and then I work with the "exists"-command:

Load *,

IF (EXISTS(key, (Name)), Name) as company_Exists

resident Table2;

But this doesn't work, because the key "Toys WorldGmbH&CoMusterwegMunich" <> "Test"

Do you have an idea? Can I use the exists command with a wildcard function?

Thanks!

[Miguel_Angel_Baeyens]

Hi,

Sure, you have Wildmatch() function, although the accuracy of results depends very much on how do you build that key.

=wildmatch('Company&City&Street', '*City*')

will return 1 as the argument passed matches the key.

[vonpape]

Hi,

it doesn't work, because i assume, that only the strings "'Company_name1' and 'name' are compared with another

Table1:
LOAD * INLINE [
 Company_name1, Company_name2, street, city
 ABC, Company, Dorfstrasse 5, Hamburg
 Test, Aktiengesellschaft, J.-F.Street, Washington
 Toys World, GmbH&Co, Musterweg, Munich
];
Table2:
LOAD * INLINE [
 Name, Adress
 Microsoft AG, Redmond
 AaBbCc, Dorfstrasse 5 Hamburg
 Toys World GmbH, München
 Test, New York
];

Load *,
IF (Wildmatch('Company_name1','*Name*') = 1, Name) as Name_exist
resident Table2;