Transformer Loop - data for related records
Posted: Tue Apr 16, 2013 12:49 pm
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!
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!