Question

String Search/vLookup

  • 26 July 2017
  • 9 replies
  • 26 views
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
Rank
Userlevel 1
Badge +10

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
Rank
Badge +22

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
Rank
Userlevel 4
Badge +30

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

jdh
Rank
Badge +22

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
Rank
Userlevel 4
Badge +30
@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 :)

 

J

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

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

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
Rank
Userlevel 1
Badge +10

 

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


Community Stats

28,670
Posts
109,364
Replies
36,531
Members
Terms & ConditionsCookie 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.