Wildmatch/Substring Issue

[#1]

Greetings!

I'm not achieving results using WILDMATCH in the case below:

I have a table with customers names

Table1:

----------------------------

Customer

----------------------------

customer1

customer2

customer3

.

.

.

And I have a table with A LOT of other customers....

Table2:

----------------------------

Customer

----------------------------

blablabla customer1 blabla

bleble customer2 blebleble

bleble customer3 hahaha

I must match the customers of table1 with the ones in table 2 that have ANY word in common... the result should look like

CustomerTable1                       CustomerTable2

------------------------------------------------------------------------

customer1                                blabla customer1 bleble

customer1                                dudu customer1 dadada

customer2                                customer2 blublubblbu

customer3                                bla customer3 dadada

.

.

.

.

I used this code to perform it:

LOAD

IF(WILDMATCH(CustomerTable2,'%' & CustomerTable1 & '%') = 1

                    AND LEN(TRIM(CustomerTable2) <> 0)

                              AND LEN(TRIM(CustomerTable1 ) <> 0)

                                        , 'POSSIBLE MATCH...','NO MATCH AT ALL...')                                                                                                                                                                                    AS CustomerWildMatched

FROM TABLE1+TABLE2

But have not worked at all.... any thoughts about it.... it is very urgent!!!

Thank you guys in advance!!!

[Oleg_Troyansky]

Hi!

the syntax LOAD ... FROM TABLE1 + TABLE2 is incorrect...

In order to compare the two, you have to join the two tables with a Cartesian Join (each Customer1 is paired with each Customer2). Depending on the size of your tables, this may be posisble, or not... If you can do it, then you can reload "resident", withe the condition like this:

where index(Customer2, Customer1) > 0

which means that the text Customer1 can be found anywhere within the text Customer2. Or, something using wildmatch...

If your tables are too big for a Cartesian Join, the only other way to match them like this is to create a loop, running through all the values of Customer1, assigning those valies one by one to a variable, and loading those rows from Customer2 that fulfill the same condition, this time using the variable instead of Customer1. THis might be a long process, depending on the number of Customer1 values.

good luck!

Oleg