+1
123 MAIN ST
The other table has the format of:
123 MAIN STREET VANCOUVER
I know that I can change the first to be exactly the same as the second, but then I have to do the same with
123 W 1ST AV
1323 W 1ST AVENUE VANCOUVER
...etc.
Is there a way to do an SQL-like "IN" for the merge? That is, to merge where the first address is IN the second?
Thanks
+13
You can use SQL on the fly with the inlineqierier.
See:
http://docs.safe.com/fme/html/FME_Transformers/Default.htm#Transformers/inlinequerier.htm
+1
+13
http://fmestore.safe.com/transformers/FuzzyStringComparer.htm
+17
If the two tables are contained in the same database, you can also create a joined table using the SQLCreator or the SQLExecutor.
"cross join" is the point. But the concrete SQL statements are different depending on the database system.
An example for MS-Access; assume table1 has ShortAddress field and table2 has LongAddress field.
-----
select t1.ShortAddress, t2.LongAddress
from table1 as t1, table2 as t2
where t2.LongAddress like (t1.ShortAddress & '*')
-----
Alternatively, a series of several transformers including FeatureMerger, ListSearcher and ListIndexcer could be used.
1) FeatureMerger (FME 2013 SP2 or later)
table1 --> REQUESTOR
table2 --> SUPPLIER
Join On: Requestor = 1, Supplier = 1 (the same constant to perform unconditional merging)
Process Duplicate Suppliers: Yes
Supplier List Name: _candidate
2) ListSearcher: Input MERGED features from the FeatureMerger.
List Attribute: _candidate{}.LongAddress
Search For: ^@Value(ShortAddress).*
Index Attribute: _list_index
Search Type: First regular expression match
3) ListIndexer: Input FOUND features from the ListSearcher
List Attribute: _candidate{}
List Index: _list_index
There is more than one way to skin a cat.
Takashi
+17
The SQLCreator with this statement works.
-----
select t1.*, t2.*
from table1 as t1, table2 as t2
where t2.LongAddress like (t1.ShortAddress + '%')
-----
+3
u can create a merge-attribute using a creator wherin u
use a tcl string function.
Like[string compare str1 strg2] , [string match str1 str2]
a range of usefull functions there.
I assume your tables are not in same order, else there would be no point to your question, you''l have to do full catresian product to find the matches(?)
+1
+17
FeatureMreger (unconditional merging) --> ListExploder --> Tester
Test Clauses: <full address> | Begins With | <short address>
"Like", "Matches Regex" or "Contains" could be a option depending on the situation. Won't hurt a real cat :-)
+3
But first u need to make all the combo's.
A cartisian product.
Like mr.Takashi said, unconditionally merge the tables.
Then u could build Str1 x Str2
(One way to do this i using a listbuilder using with a group by on Str1 and Str2. Then dump the duplicates, with a listduplicateremover.
Now what u are seeking is somewhere in this list.)
Or activate the list in the merger; again what u are seeking is somewhere in the list.
Use something like
Like[string compare str1 strg2] , [string match str1 str2]
or regular fme stringsearch transformers.
Basicaly samish as mr. Takashi suggestion.
+3
It is all moot to Schroedingers cat.... ;)
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
