DSXchange

Hi,

I am fairly new to DataStage and I was wondering if some could please help me out with this problem. I have a sequential file for which the fields within it are of fixed width (no separators). Within an individual record there are structures that repeat themselves multiple times and I wish to normalise these and put them in to their own table. The illustration below shows what I am trying to achieve.I have used the names Field1, Field2, Field3 to indicate the repeating structure. Also, for the purposes of the illustration I have used a comma to delimite the fields

INPUT DATA
----------

Key, Field1, Field2, Field3, Field1, Field2, Field3, Age, DOB .....
10000, X, Y, Z, A, B, C, 24, 19/06/1982 .....

I want the above data to be split in to the following two tables.....

OUTPUT_TABLE_1
--------------

Key, Field1, Field2, Field3
10000, X, Y, Z
10000, A, B, C

OUTPUT_TABLE_2
--------------
Key, Age, DOB .....
10000, 24, 19/06/1982 .....

In the file I am working with, some of these structures repeat themselves 35 times so I was looking for something a little more efficient then having 35 separate output links. Any ideas would be appreciated,

Regards

Welcome aboard. :D

What you are seeking to do is called a "horizontal pivot" and is performed by the Pivot stage. Read its manual (pivot.pdf) for a good description of how to use it.

Thanks Ray, I will have a good read through the doco.

Cheers