Merging two data sets using lookup/contain

Hi all,

I'm trying to merge two data-sets when they don't have a common field.

What I would like to do is merge them using keywords that I have in one of the lists, so it should be a kind of a Feature merger with "contains" function, but so far I'm not really successful in finding a solution.

Here's an example:

List of IDs and keywords (1st data set):

ID
Keyword

111
Mc'Donalds

545454
Starbucks

1234
KFC

222
Burger King

343434
Golden Tulip

List of places that I would like to assign ID to (2nd data set):

Place_name
ID

Mc'Donalds
Amsterdam Central

Starbucks
Airport

Stations' KFC

Piazza Burger
King

Amsterdam
Golden Tulip Hotel

So in the end it should simply look like this:

Place_name

Mc'Donalds Amsterdam Central111Starbucks Airport545454Stations' KFC1234Piazza Burger King222Amsterdam Golden Tulip Hotel343434

Any suggestions?

Thanks,

Linda

Page 1 / 1

Take a look at the following link: https://knowledge.safe.com/questions/38771/can-i-use-a-feature-merger-to-merge-partial-names.html

the InlineQuerier is your friend:

select * from table_a join table_b on table_a.id || '%' like table_b.id || '%'

Take a look at the following link: https://knowledge.safe.com/questions/38771/can-i-use-a-feature-merger-to-merge-partial-names.html

the InlineQuerier is your friend:

select * from table_a join table_b on table_a.id || '%' like table_b.id || '%'

Great, thanks @stalknecht!

InlineQuerier is definitely my friend now

Take a look at the following link: https://knowledge.safe.com/questions/38771/can-i-use-a-feature-merger-to-merge-partial-names.html

the InlineQuerier is your friend:

select * from table_a join table_b on table_a.id || '%' like table_b.id || '%'

Hi @stalknecht,

I'm running into some issues when using above piece of SQL - to me this seems more like a "begins with..." instead of a "contains" command.

E.g.

"Starbucks Airport" would match when the used keyword is "Starbucks".

but

"Schiphol Airport Starbucks" would not match.

Is there a way to update the SQL string so it also matches words that are found in the entire string instead of only at the beginning?

Cheers,

Hi @stalknecht,

I'm running into some issues when using above piece of SQL - to me this seems more like a "begins with..." instead of a "contains" command.

E.g.

"Starbucks Airport" would match when the used keyword is "Starbucks".

but

"Schiphol Airport Starbucks" would not match.

Is there a way to update the SQL string so it also matches words that are found in the entire string instead of only at the beginning?

Cheers,

Correct it should be:

select * from table_a join table_b on table_a.id  like || '%' table_b.id || '%'

Correct it should be:

select * from table_a join table_b on table_a.id  like || '%' table_b.id || '%'

Hi @stalknecht,

That seems to result in an error:

InlineQuerier(InlineQueryFactory): Error preparing database query: near "||": syntax error Unable to execute statement

InlineQuerier(InlineQueryFactory): Database was unable to prepare query 'SELECT *

FROM "Sheet1" JOIN "brands" ON "Name" like || '%' "brand description" '%' ||

InlineQueryFactory instance was not properly defined. Consult logfile for details.

InlineQuerier(InlineQueryFactory): A fatal error has occurred. Check the logfile above for details

A fatal error has occurred. Check the logfile above for details

Correct it should be:

select * from table_a join table_b on table_a.id  like || '%' table_b.id || '%'

select * from table_a join table_b on table_a.id like '%' || table_b.id || '%'

worked this way

Reply

Community Stats

Latest FME

Reply

Community Stats

Latest FME

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded