Skip to main content
  • EN

I was wondering if anyone here can help me out with an FME expression that is part of a larger FME puzzle. Skip ahead for the FME expression I'm trying to write, or read the whole thing for background information. As most of us know, there is probably more than one solution to the overall puzzle.

 

Background information about the FME puzzle:

I have a Table1 with a column called USERNAME that is a list of names, and a column called called USERPHONE that is a list of associated phone numbers. I have a different Table2 with a column called BLDG_INSP with a list of names, and an empty column called BLDG_INSP_PHONE. Based on the information in Table1, I want to populate BLDG_INSP_PHONE. Initially I tried various Joins and conditional statements in AttributeManager, but no luck.

 

So, I combined USERNAME and USERPHONE into a single column called USERNAMEUSERPHONE, separated by a comma, to use as a bridge.

 

Ex: <Anthony Stokes,123-456-789>

 

The idea is that I can write a conditional statement that checks to see if the BLDG_INSP name is USERNAMEUSERPHONE column, and if so, retrieve the phone from USERNAMEUSERPHONE and place it in in the BLDG_INSP_PHONE column.

 

Conditional statement with FME expressions:

I'm new to using FME expressions in AttributeManager's conditional statements. The conditional statement I'm writing in AttributeManager goes something like: For every value in BLDG_INSP column, if the value matches the string to the left of a comma in USERNAMEUSERPHONE column, return the string to the right of the value in USERNAMEUSERPHONE, and place it in BLDG_INSP_PHONE. See the attached screenshot for what I have created so far. FME errors out on the expression.

 

Further testing showed that the expression below...

 

@Left(@Value(USERNAMEUSERPHONE),(@FindString(@Value(USERNAMEUERPHONE),",")))

 

...returns everything to the left of the comma, including the comma, when I should be returning what is to the left of the comma, not including the comma. Maybe that is the problem? Maybe this is the wrong type of solution?

 

If you've made it this far, thanks for reading!

I suggest using the FeatureJoiner for this.

Left join on BLDG_INSP = USERNAME, next copy the value of USERPHONE to BLDG_INSP_PHONE.

Or in this simple case: rename the attributes of Table1, and then join.

FeatureJoiner_demo

I suggest using the FeatureJoiner for this.

Left join on BLDG_INSP = USERNAME, next copy the value of USERPHONE to BLDG_INSP_PHONE.

Or in this simple case: rename the attributes of Table1, and then join.

FeatureJoiner_demo

I also want to add that you should convert the names in both tables to UPPERCASE before joining and using them as join attributes.

I also want to add that you should convert the names in both tables to UPPERCASE before joining and using them as join attributes.

Thank you. Do you know why the row count of my Table2 increases from 4687 to 18,105 after the feature joiner? I'm trying to keep that row count down. Also, most of the geometry associated with Table2 disappears after the feature joiner. It looks like my settings are identical so I'm thinking it's a result of table 2 having spatial data? I need to preserve the geometry along the pipeline.

I also want to add that you should convert the names in both tables to UPPERCASE before joining and using them as join attributes.

There was no mention of any spatial data in your initial question, this changes matters a bit. You may have better results with FeatureMerger.

 

As for the increased row count: FeatureJoiner creates a joined feature each time a Left feature is matched to a Right feature through its keys. The number of output features produced will depend on whether or not multiple Left and Right features match.

So there must be multiple matches. Which is strange, as one would expect that only some users from Table1 would be present in Table2.

To just focus on your question about the "Left" function:

@Left(@Value(USERNAMEUSERPHONE),(@FindString(@Value(USERNAMEUERPHONE),",")))

 

The function has two inputs. A string and a number of characters.

Then you use the "FindString" function to return the number of characters. This returns 1 character to much because you search for the position of the ",". So just subtract one.

 

@Left(@Value(USERNAMEUSERPHONE),(@FindString(@Value(USERNAMEUERPHONE),","))-1)

 

But the others probably pointed out that there are more ways to Rome as we say in dutch.

Reply


Community Stats

30,176
Posts
114,544
Replies
37,837
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.