Skip to main content
Solved

Creating an ID number

C
Forum|alt.badge.img+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!

Best answer by redgeographics

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)

 

 

 

View original
Did this help you find an answer to your question?

4 replies

redgeographics
Celebrity
Forum|alt.badge.img+49
  • redgeographicsCelebrity
  • Celebrity
  • Best Answer
  • April 15, 2022

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
Forum|alt.badge.img+2
  • chris28
  • Author
  • April 18, 2022

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
Celebrity
Forum|alt.badge.img+49
chris28 wrote:

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
Forum|alt.badge.img+2
  • chris28
  • Author
  • April 19, 2022
redgeographics wrote:

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


Helpful Members This Week

Community Stats

31,153
Posts
118,098
Replies
38,771
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.