Transformer Loop - data for related records

[jall2013]

Good day all,

I'm dealing with a new situation within our source data and would appreciate any guidance on completing the following requirement, either through a Transformer loop or other appropriate method.

The situation we have is that our input data may reference another record within the same data set as its parent, and that parent record may in turn reference another record within the set as its parent, and so on and so forth. We know that we've hit an 'ultimate' level parent when the Child ID = Parent ID on the record. The simplified file layout, and an example, would be:
ChildID|ChildName|ParentID
01|Ultimate|01
02|Immediate|01
03|Sub|02

The intent is to create a new field field for each record that contains any and all parent name values, separated by a delimiter such as ':' - example output from the above set would be:
ChildID|ChildName|ParentID|ParentNames
01|Ultimate|01|
02|Immediate|01|Ultimate
03|Sub|02|Ultimate:Immediate

In past situations where the source data was a database, we've used stored procedures to calculate the appropriate values and stored those for future reads, however I'm hoping we can implement similar logic within DS for text file sources.

Thank you all for your consideration and feedback!

[ray.wurlod]

Welcome aboard.

DataStage is really set up to stream data through it. Recursion and going back multiple levels is not really supported, without multiple passes over the data.