Skip to main content
  • EN

I'm trying to get to the bottom of this non-spatial data issue and am racking my brain to no avail...

I have a table of data that looks as follows...

IDFacilityCode1A001,D0471D047,P001,G00512D001,D047,F0012F001,G0092P001,A0013G0053G0054A001

Notice that the FacilityCode may be duplicated between records for any particular ID. I need my final table to just be a DISTINCT list of IDs with an alphabetized list of unique FacilityCodes, so it should look like this...

IDFacilityCode1A001,D047,P001,G0052A001,D001,D047,F001,G009,P0013G0054A001

Theoretically, I'm thinking I might be able to use regex to extrapolate the unique FacilityCodes including the comma-separated values for the records with multiple FacilityCodes (with a StringSearcher? Not even sure what my regex would look like), then a ListDuplicateRemover, a ListSorter (to alphabetize), then a ListConcatenator to bring my list back to a single attribute.

I'm not even certain that could all work correctly while maintaining a grouping on the ID. I thought about using a SELECT GROUP_CONCAT(FacilityCode,",")) sql statement with an inlinequerier, then going from there...but I'm not certain how well that would work either.

Thanks in advance for any help! I'll keep this post updated if I find any solutions on my own. I'm running a 2015 version of FME via ArcGIS 10.3 data interop.

Hi @bwasserstein,

Please have a look at this image to build your workflow (I'm using 2018 so can't send over a template but believe this should still be doable in 2015):

First I used an Aggregator to combine each ID, then I used an Attribute Splitter to generate a list feature and remove attributes with the ListDuplicateRemover, before joining the list back together with the Concatenator.

Workspace:

Output:

(You can just add a sorter to the end to reorder these!)

Hi @bwasserstein,

Please have a look at this image to build your workflow (I'm using 2018 so can't send over a template but believe this should still be doable in 2015):

First I used an Aggregator to combine each ID, then I used an Attribute Splitter to generate a list feature and remove attributes with the ListDuplicateRemover, before joining the list back together with the Concatenator.

Workspace:

Output:

(You can just add a sorter to the end to reorder these!)

Beautiful! Thank you, Holly...that works just fine in 2015 & was an easy solution I should have thought of. Somehow I ended up with leading commas for some of my "Code" concatenations, but a testfilter (@Value(Code) BEGINS_WITH ,) leading into a substring extractor took care of that. Fully handled with FME transformers!

 

 

I'm very grateful for you all at Safe & this forum 🙂 Thanks again.

 

 

Beautiful! Thank you, Holly...that works just fine in 2015 & was an easy solution I should have thought of. Somehow I ended up with leading commas for some of my "Code" concatenations, but a testfilter (@Value(Code) BEGINS_WITH ,) leading into a substring extractor took care of that. Fully handled with FME transformers!

 

 

I'm very grateful for you all at Safe & this forum 🙂 Thanks again.

 

 

Try setting 'Yes' to the Drop Empty Part parameter in the AttributeSplitter (or the Drop Empty or Null Elements in the ListConcatenator) in order to remove the excess commas.
Try setting 'Yes' to the Drop Empty Part parameter in the AttributeSplitter (or the Drop Empty or Null Elements in the ListConcatenator) in order to remove the excess commas.
That did the trick as well. Thanks @takashi.

Reply


Community Stats

30,159
Posts
114,480
Replies
37,807
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.