Normalizing Data
Normalizing Data
This is a topic for an orphaned message.
There are three good ways to do this. In all cases, capture the header line information into one or more stage variables.
An easy-to-implement way is to read the line as a single column, convert the delimiters into value-marks using Convert(",", @VM, InputColumn) and load this into a UV table that contains a multi-valued column to receive it. Then select from the UV stage normalized on that column.
Another way is to create your own custom transform routine.
The third way is extremely quick. You use an intermediate sequential file. You load this with a single column, which is derived as head:col1:EOL:head:col2:EOL:head:col3:EOL ... When you read from this sequential file, the EOL (end of line) characters have gone and you get your desired result. EOL for UNIX is Char(13), for Windows NT is Char(13):Char(10). I would recommend doing this in a Routine rather than doing the whole thing in an expression; there is a limit of 8KB on the length of an expression.
This is one of the design techniques taught on the "Advanced DataStage" class in Asia-Pacific (DS-ADV, which is not the same as DS305).
-----Original Message-----
From: Bob_2_Whiteside@sbphrd.com [mailto:Bob_2_Whiteside@sbphrd.com]
Sent: Thursday, 26 April 2001 06:14
To: informix-datastage@oliver.com
Subject: informix-datastage@oliver.com
Greetings,
I need to transform an input sequential file that has monthly amounts in comma, delimited format (likely a rolling 18 month maximum) to output records that will contain a single months data. So that 1 input record will generate 18 output records. The date (mm/yyyy) for each month will be found in a column header line at the beginning of the file and will be included on each fact record. Is there a good way to do this?
Any help would be appreciated. By the way, Im using DS 4.0 on a Sun/Solaris server. Thanks, Bob
An easy-to-implement way is to read the line as a single column, convert the delimiters into value-marks using Convert(",", @VM, InputColumn) and load this into a UV table that contains a multi-valued column to receive it. Then select from the UV stage normalized on that column.
Another way is to create your own custom transform routine.
The third way is extremely quick. You use an intermediate sequential file. You load this with a single column, which is derived as head:col1:EOL:head:col2:EOL:head:col3:EOL ... When you read from this sequential file, the EOL (end of line) characters have gone and you get your desired result. EOL for UNIX is Char(13), for Windows NT is Char(13):Char(10). I would recommend doing this in a Routine rather than doing the whole thing in an expression; there is a limit of 8KB on the length of an expression.
This is one of the design techniques taught on the "Advanced DataStage" class in Asia-Pacific (DS-ADV, which is not the same as DS305).
-----Original Message-----
From: Bob_2_Whiteside@sbphrd.com [mailto:Bob_2_Whiteside@sbphrd.com]
Sent: Thursday, 26 April 2001 06:14
To: informix-datastage@oliver.com
Subject: informix-datastage@oliver.com
Greetings,
I need to transform an input sequential file that has monthly amounts in comma, delimited format (likely a rolling 18 month maximum) to output records that will contain a single months data. So that 1 input record will generate 18 output records. The date (mm/yyyy) for each month will be found in a column header line at the beginning of the file and will be included on each fact record. Is there a good way to do this?
Any help would be appreciated. By the way, Im using DS 4.0 on a Sun/Solaris server. Thanks, Bob