Solved

Finding duplicate value and looping through each one to increment the value and make unique

  • 29 April 2021
  • 6 replies
  • 20 views

The problem:

 

We have duplicate ID's across various tables, an example being:

 

Table 1

ID 1 = UNI-ID1-234211

ID 2 = UNI-ID1-234211

 

Table 2

ID 1 = UNI-ID2-230011

ID 2 = UNI-ID2-230011

ID 2 = UNI-ID2-230011

 

I want to find the duplicate ID's and then increment the last part of the ID, but retain first lowest value. For example:

 

Table 1

ID 1 = UNI-ID1-234211

ID 2 = UNI-ID1-234212

 

Table 2

ID 1 = UNI-ID2-230011

ID 2 = UNI-ID2-230012

ID 2 = UNI-ID2-230013

 

I've isolated the duplicates with a Matcher already. I've split the ID based on hyphen to get me 3 parts to the ID, _list{2} gives me the last part.

 

I assume I'll need to push it through a looping custom transformer maybe? or can it be done with Listing techniques too?

 

Thanks for your help,

icon

Best answer by ebygomm 29 April 2021, 14:19

View original

6 replies

Badge +10

No need for looping, if you've already split the ID into a list, you can use a counter with the Counter Name (Group By) set to ID and starting from zero. Then you can use an attributecreator to add the count onto the third list item and recreate the id with hyphens

CaptureCapture1

No need for looping, if you've already split the ID into a list, you can use a counter with the Counter Name (Group By) set to ID and starting from zero. Then you can use an attributecreator to add the count onto the third list item and recreate the id with hyphens

CaptureCapture1

Hey @ebygomm​ , what version of FME does the counter have a group by function? I'm using FME 2020.0.3.0 and there's no group by option there. Thank you for your answer though!

Hey @ebygomm​ , what version of FME does the counter have a group by function? I'm using FME 2020.0.3.0 and there's no group by option there. Thank you for your answer though!

Your answer helped me solve it without the counter, I added a ListBuilder to group by my ID. Then ListExploder, and used the element counter to evaluate the last part of my ID. Thank you!

Badge +10

Hey @ebygomm​ , what version of FME does the counter have a group by function? I'm using FME 2020.0.3.0 and there's no group by option there. Thank you for your answer though!

The counter has always had a group by it's just not very well named. If you set an attribute for the Counter Name parameter, this will be a group by. I believe from 2021 this is now called Group ByCapture

@ebygomm​  solution works, but for those who don't have a version of FME that has Group By on the Counter.

 

I essentially used a ListBuilder grouped by ID. Then ListExploder to get the element_count which can be added onto the last part of my ID.

 

 

Badge +10

@ebygomm​  solution works, but for those who don't have a version of FME that has Group By on the Counter.

 

I essentially used a ListBuilder grouped by ID. Then ListExploder to get the element_count which can be added onto the last part of my ID.

 

 

As above, everyone has a version of FME that has a group by on the counter, even if it's not called Group By. Set the Counter Name to the attribute you want to group by

Reply