Skip to main content
Question

String Search/vLookup

J

Hi all,

I have a question regarding string searching, which is slighlty related to a vlookup process.

I have two datasets.

One is a shapefile of a pipe network that has a concatenated output from civil design package. The field is named 'Partsizename' and contains values like "305 mm Concrete Pipe" or "151 NB UPVC PN". The other is an excel spreadsheet containing a field named 'Material', which lists 40 common pipe materials (e.g. "Concrete", "UPVC", "Copper").

I want to search Partsizename for the values listed in Material, and if any match, output the matching string into another field. E.g. For a row in Partsizename that contains "305 mm Concrete Pipe", 'Concrete' is one of the materials listed in 'Material', so the output will be 'Concrete'.

Thanks in advance for any help!

9 replies

ebygomm
Influencer
Forum|alt.badge.img+32
  • ebygommInfluencer
  • Influencer
  • July 26, 2017

Does the data in the excel spreadsheet change or is it fixed?

If fixed you can probably use the detail to write a regular expression to return the value you are interested in from the Partsizename attribute and store in a new attribute

jdh
Contributor
Forum|alt.badge.img+28
  • jdhContributor
  • Contributor
  • July 26, 2017

I would use an inlineQuerier with the SQL statement:

 

SELECT * FROM Pipes LEFT JOIN Materials ON INSTR(Pipes.Partsizename, Materials.Material) > 0

 

 

Two input ports, one connected to the pipe shapefiels (Pipes), the other to the Material excel file (Materials)
danilo_fme
Evangelist
Forum|alt.badge.img+44
  • danilo_fmeEvangelist
  • Evangelist
  • July 26, 2017

Hi @jordan_miller,

I created a template Workflow and simulated with polygon.

You can see below the two Readers and create a commun attribute between two readers ( Transformer AttributeCreator ).

In the end i generate the shape file with a new attribute Result.

Thanks,

Daniloworkspace-shp-and-xls.fmwt

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
jdh
Contributor
Forum|alt.badge.img+28
  • jdhContributor
  • Contributor
  • July 26, 2017
danilo_fme wrote:

Hi @jordan_miller,

I created a template Workflow and simulated with polygon.

You can see below the two Readers and create a commun attribute between two readers ( Transformer AttributeCreator ).

In the end i generate the shape file with a new attribute Result.

Thanks,

Daniloworkspace-shp-and-xls.fmwt

@danilo_inovacao you don't need to create an attribute for the FeatureMerger, you can just set the requestor and supplier to the same hardcoded value, ex. 1.

 

danilo_fme
Evangelist
Forum|alt.badge.img+44
  • danilo_fmeEvangelist
  • Evangelist
  • July 26, 2017
jdh wrote:
@danilo_inovacao you don't need to create an attribute for the FeatureMerger, you can just set the requestor and supplier to the same hardcoded value, ex. 1.

 

Oh yes @jdh, thanks to remenber me :)

 

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
J
ebygomm wrote:

Does the data in the excel spreadsheet change or is it fixed?

If fixed you can probably use the detail to write a regular expression to return the value you are interested in from the Partsizename attribute and store in a new attribute

 

Hi @egomm, good question; I initially searched for regular expressions which works well, but the spreadsheet has the potential to change, hence why I was interested in searching the entire materials field. Thanks for your response
J
jdh wrote:

I would use an inlineQuerier with the SQL statement:

 

SELECT * FROM Pipes LEFT JOIN Materials ON INSTR(Pipes.Partsizename, Materials.Material) > 0

 

 

Two input ports, one connected to the pipe shapefiels (Pipes), the other to the Material excel file (Materials)

 

Hi @jdh, thanks for your response! That makes sense, will give it a go
J
danilo_fme wrote:

Hi @jordan_miller,

I created a template Workflow and simulated with polygon.

You can see below the two Readers and create a commun attribute between two readers ( Transformer AttributeCreator ).

In the end i generate the shape file with a new attribute Result.

Thanks,

Daniloworkspace-shp-and-xls.fmwt

 

Hi @danilo_inovacao, thanks for taking the time to post your workflow. Will have a look at it
ebygomm
Influencer
Forum|alt.badge.img+32
  • ebygommInfluencer
  • Influencer
  • July 26, 2017
jordan_miller wrote:

 

Hi @egomm, good question; I initially searched for regular expressions which works well, but the spreadsheet has the potential to change, hence why I was interested in searching the entire materials field. Thanks for your response
If the spreadsheet can change, you can aggregate all attributes to create a regular expression merge it with the other features and then use a string searcher to set the new attribute

 

Reply


Helpful Members This Week

Community Stats

31,140
Posts
118,062
Replies
38,759
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.