Skip to main content
Solved

Do I need a list of a list attribute? Regex to extrapolate to a list attribute?

  • September 27, 2018
  • 4 replies
  • 29 views
bwasserstein
Participant
Forum|alt.badge.img

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.

Best answer by hollyatsafe

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!)

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

H
Forum|alt.badge.img+2
  • hollyatsafe
  • 719 replies
  • Best Answer
  • September 27, 2018

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!)

bwasserstein
Participant
Forum|alt.badge.img
  • bwassersteinParticipant
  • Author
  • Participant
  • 20 replies
  • September 27, 2018

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.

 

 

takashi
Celebrity
  • takashiCelebrity
  • 7842 replies
  • September 28, 2018
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.
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
bwasserstein
Participant
Forum|alt.badge.img
  • bwassersteinParticipant
  • Author
  • Participant
  • 20 replies
  • September 28, 2018
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.

Community Stats

32,061
Posts
121,725
Replies
39,688
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.