Wildmatch/Substring Issue

Fri, 19 Aug 2011 13:50:27 GMT

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!!!

Wildmatch/Substring Issue

Oleg_Troyansky — Fri, 19 Aug 2011 16:44:02 GMT

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

topic Wildmatch/Substring Issue in QlikView

Wildmatch/Substring Issue

Wildmatch/Substring Issue