Solved

Creating an ID number

  • 15 April 2022
  • 4 replies
  • 107 views
C
Badge +2

I have a FME worbench that is reading an excel file and merging it with data in a SDE geodatabase. For each record in the table I need to create an ID field that consists of a consistent initial 3 letters depending on the company, then a 2 digit year, and then 4 digits. It would need to be unique as well.

  • I would need to find the last 4 digit number used by the company and add one to it.
  • I would then need to put this all together into one ID field attribute.

Any ideas of how to do this would be appreciated!

icon

Best answer by redgeographics 15 April 2022, 10:26

View original

4 replies

redgeographics
Rank
Userlevel 4
Badge +25

Okay, this was a fun challenge!

I've set up a small sample, attached here as a template (FME 2021.2)

Screenshot 2022-04-15 at 10.21.08I've created some source data, just the id's of a fictional existing dataset:

RED220001
RED220002
BLU220001

So a new record being added in 2022 for company RED would have to get ID RED220003

 

I use an AttributeSplitter to split the original ID into 3 new attributes: company, year and number. Then sorting and sampling so that I only keep the record with highest number per company per year. Join that to the new data (company is being set by a user parameter, year is coming from a timestap) and then generate a new id:

@Value(company)@Value(year)@PadLeft(@Evaluate(@Value(number)+1),4,0)

 

 

 

C
Badge +2

I'm so glad you like a challenge! Thanks so much!! A couple of questions regarding your workflow. You rename the id field and then split it and then remove it. You then create it again and give it a value. Just so I understand - what is the purpose of removing the attribute? If you have multiple other features going in you wouldn't want to remove it the attribute I assume? Also, when you are calculating the value of the id field using @Value(company)@Value(year)@PadLeft(@Evaluate(@Value(number)+1),4,0)

So +1 gives you the next number, 4 is the number of numbers, but what is the 0 for? (Sorry is this is an obvious answer)

redgeographics
Rank
Userlevel 4
Badge +25

I'm so glad you like a challenge! Thanks so much!! A couple of questions regarding your workflow. You rename the id field and then split it and then remove it. You then create it again and give it a value. Just so I understand - what is the purpose of removing the attribute? If you have multiple other features going in you wouldn't want to remove it the attribute I assume? Also, when you are calculating the value of the id field using @Value(company)@Value(year)@PadLeft(@Evaluate(@Value(number)+1),4,0)

So +1 gives you the next number, 4 is the number of numbers, but what is the 0 for? (Sorry is this is an obvious answer)

I'm removing the attribute to avoid confusing myself down the road 😅 Or to be more exact, to be absolutely certain the new attribute ID I'm creating is not accidentally filled with incorrect data. That shouldn't happen, but I prefer to be absolutely certain in these cases.

 

The 0 is a parameter of the @PadLeft function and specifies to pad with 0s. The output of the Counter has no leading zeroes, so that's why I'm padding it.

C
Badge +2

I'm removing the attribute to avoid confusing myself down the road 😅 Or to be more exact, to be absolutely certain the new attribute ID I'm creating is not accidentally filled with incorrect data. That shouldn't happen, but I prefer to be absolutely certain in these cases.

 

The 0 is a parameter of the @PadLeft function and specifies to pad with 0s. The output of the Counter has no leading zeroes, so that's why I'm padding it.

Thank you. This was exactly what I needed. I appreciate the extra info. This is the greatest community for getting assistance when you're just learning.

Reply


Community Stats

28,663
Posts
109,330
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.