AnsweredAssumed Answered

Combining data from multiple rows on a Flat File into a single row grouped by ID

Question asked by AndreiSouchinski5081 on Jan 24, 2017
Latest reply on Jan 24, 2017 by AndreiSouchinski5081

I have a Flat File with an ID and variable occurrence of comments:

 

ID,Comment
T-123,CommentA
T-123,CommentB
T-123,CommentC
T-124,CommentA
T-124,CommentB
T-125,CommentC

 

I am looking to combine the rows by the ID and concatenate the corresponding Comment values on the output Flat File delimited by a semi-colon:

 

ID,Comment
T-123,CommentA;CommentB;CommentC
T-124,CommentA;CommentB
T-125,CommentC

 

I've tried converting the FF to an XML so I can group the Comments alongside the same ID, I got as far as getting the following XML output:

 

When I try to map the above back to a Flat File - I use a Concat function to join the comments for the same ID, but I guess the parser doesn't see all the comments at once an the output is broken out by comment still.

 

Here is the Process:

Concat

 

Do I need a custom script to concatenate the multiple comments on the output? Or have I over thought this - and there is an easier way of achieving what I need.

Outcomes