Skip to main content
Solved

Merging two data sets using lookup/contain

  • July 13, 2018
  • 6 replies
  • 28 views
L

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

ID

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

 

Any suggestions?

 

 

Thanks,

 

Linda

Best answer by stalknecht

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 || '%'
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

6 replies

stalknecht
Contributor
Forum|alt.badge.img+21
  • stalknechtContributor
  • Contributor
  • 305 replies
  • Best Answer
  • July 13, 2018

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 || '%'
L
  • llindish
  • Author
  • 10 replies
  • July 13, 2018

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

 

E
Forum|alt.badge.img
  • edhere
  • 60 replies
  • July 16, 2018

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,

 

Ed

 

 

 

stalknecht
Contributor
Forum|alt.badge.img+21
  • stalknechtContributor
  • Contributor
  • 305 replies
  • July 16, 2018
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,

 

Ed

 

 

 

Correct it should be:

 

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

 

E
Forum|alt.badge.img
  • edhere
  • 60 replies
  • July 16, 2018
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

 

 

L
  • llindish
  • Author
  • 10 replies
  • July 16, 2018
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

 

Community Stats

32,085
Posts
121,845
Replies
39,724
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.