Solved

loose match

G
Badge
Good afternoon,

 

 

I have this issue come up now and again and I'd like to be able to solve it. But I frequently get excel spreadsheets that neet to be mached to a Point Layer in SDE. Normally I take the geometry of the SDE layer and keep the attributes of the spreadsheet. I normally use the FeatureMerger transformer. My problem is sometimes the number I use to match on is sometimes odd in the spreadsheet such as having trailing zeros or just any number of extra characters (normally 8000+ rows). So question being is there a way to do a "Loose match" on the fields to come up with a match? Thanks!
icon

Best answer by takashi 19 May 2015, 16:57

View original

8 replies

takashi
Rank
Userlevel 2
Badge +17
Hi,

 

 

If you need to ignore all trailing zeros and non-digit characters in the number coming from the spreadsheet, one possible way is to remove them using a StringReplacer with this setting beforehand.

 

-----

 

Test to Match: (.*[1-9])([^1-9]*)$

 

Replacement Text: \\1

 

Use Regular Expressions: yes

 

 

But it doesn't work for a case where the number coming from the SDE table ends with "0", e.g. "120". If there could be such a case, you will have to think of other approaches.

 

 

Takashi
G
Badge +3
..i bet he meant leading zero's  
G
Badge
Thanks for the replies. In some instances it would be leading. Since this does not seem to work on SDE what would be the Regex for adding 4 zeros to a 10 digit number so as to modify the excel side? 
takashi
Rank
Userlevel 2
Badge +17
Could you please show us some examples of the original string and the preferable result after modifying?
G
Badge
Yes, in this case nearly all the records are 9 to 10 characters long. 3507725552 -> 35077255520000 or 307170548 -> 30717054800000. 
takashi
Rank
Userlevel 2
Badge +17
The StringFormatter might help you.

 

Format String: -014s

 

 

The format string means:

 

- the minimum number of characters should be 14

 

- add trailing zeros if the original number of characters is less than 14. 

 

See the help on the StringFormatter to learn more about the fomat string.

 

G
Badge
Thankyou, I can't believe I didn't see this when looking through the transformers. But makes perfect sense. 
takashi
Rank
Userlevel 2
Badge +17
A little bit advanced technique.

 

You can also set this expression directly to the "Join On" parameter of the FeatureMerger.

 

-----

 

@Format(%-014s,@Value(the_number_attribute_name))

 

-----

 

 

@Format is one of the FME String Functions, the behavior is almost equivalent to the StringFormatter transformer.

 

String Functions (http://docs.safe.com/fme/html/FME_Transformers/FME_Transformers.htm#transformer_parameters/StringFunctions.htm)

 

 

FYI

Reply


Community Stats

28,753
Posts
109,617
Replies
36,587
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.