Greetings,
I need to read a sequential file extract from a data system. The file contains several lines of indented heirarchy before reaching the one or more true data lines. When I hit a data line, I need to create an output record with the comma delimited data from the data line plus all of the previous heirarchy data. An example
1234567 >
Department line
abcde > Cost
center line
building2 >
Location line
Bob,10,20,30,40,.... > Data line
Output line
1234567,abcde,building2,Bob,10,20,30,40,...
I tried to use stage variables, but they seem to reset prior to getting to the data line. Any help would be appreciated. By the way, Im using DS 4.0 on a Sun/Solaris server. Thanks, Bob
informix-datastage@oliver.com
Bob,
Using stage variables should work - however you must ensure that you only assign them once (or per grouping).. Also I assume that you are reading in the entire row as a single column and parsing it. This is usually very effective for complex structures.
Stage variable example
Derivation
| Variable Name | Description
============================================================================
===========
if inlink.row = header row then 1 else 0 |
HeaderLineFlag | we set this so we then we can reuse it.
if headerlineflag Then inlink.row else headerline |
HeaderLine | this assigns headerline only if its a header line
if dataline then 1 else 0
| WriteFlag | this is use in the output link constraint
This assumes that we have some way to determine what the header row(s) are/is I usually am doing some sort of substring operation
e.g. if inlink.column[4,2] = 01 then 0 else 1 - this checks for the
inputlink.column starting position 4 for 2 bytes for a 01
Output link constraint: WriteFlag
This tests for WriteFlag to be 0 or 1 (when 1 it will output a row) - this is equivilent to: If WriteFlag = 1 Then 1 Else 0
Hope this helps...
Allen Spayth
----- Original Message -----
From:
To:
Sent: Tuesday, April 24, 2001 5:18 AM
Subject: informix-datastage@oliver.com
> Greetings,
>
> I need to read a sequential file extract from a data system. The
> file contains several lines of indented heirarchy before reaching the
> one or more true data lines. When I hit a data line, I need to create
> an output record with the comma delimited data from the data line plus
> all of the previous heirarchy data. An example
> 1234567 >
> Department line
> abcde > Cost
> center line
> building2 >
> Location line
> Bob,10,20,30,40,.... > Data line
>
>
> Output line
> 1234567,abcde,building2,Bob,10,20,30,40,...
>
> I tried to use stage variables, but they seem to reset prior to
> getting to the data line. Any help would be appreciated. By the way,
> Im using DS 4.0 on a Sun/Solaris server. Thanks, Bob
>
Using stage variables should work - however you must ensure that you only assign them once (or per grouping).. Also I assume that you are reading in the entire row as a single column and parsing it. This is usually very effective for complex structures.
Stage variable example
Derivation
| Variable Name | Description
============================================================================
===========
if inlink.row = header row then 1 else 0 |
HeaderLineFlag | we set this so we then we can reuse it.
if headerlineflag Then inlink.row else headerline |
HeaderLine | this assigns headerline only if its a header line
if dataline then 1 else 0
| WriteFlag | this is use in the output link constraint
This assumes that we have some way to determine what the header row(s) are/is I usually am doing some sort of substring operation
e.g. if inlink.column[4,2] = 01 then 0 else 1 - this checks for the
inputlink.column starting position 4 for 2 bytes for a 01
Output link constraint: WriteFlag
This tests for WriteFlag to be 0 or 1 (when 1 it will output a row) - this is equivilent to: If WriteFlag = 1 Then 1 Else 0
Hope this helps...
Allen Spayth
----- Original Message -----
From:
To:
Sent: Tuesday, April 24, 2001 5:18 AM
Subject: informix-datastage@oliver.com
> Greetings,
>
> I need to read a sequential file extract from a data system. The
> file contains several lines of indented heirarchy before reaching the
> one or more true data lines. When I hit a data line, I need to create
> an output record with the comma delimited data from the data line plus
> all of the previous heirarchy data. An example
> 1234567 >
> Department line
> abcde > Cost
> center line
> building2 >
> Location line
> Bob,10,20,30,40,.... > Data line
>
>
> Output line
> 1234567,abcde,building2,Bob,10,20,30,40,...
>
> I tried to use stage variables, but they seem to reset prior to
> getting to the data line. Any help would be appreciated. By the way,
> Im using DS 4.0 on a Sun/Solaris server. Thanks, Bob
>
Bob,
Another suggestion would be to use stage variables (As you tried before). However, do not overwrite the stage variables. I also assume that you are reading the entire row as a single column and parsing it when required.
Derivation
| Variable Name | Description
==========================================================================
If InLink.Row = HeaderRow Then 1 Else 0
| HeaderRowFlag | This is saved for later reference
If HeaderRowFlag Then InLink.Row Else HeaderRow |
HeaderRow | Updated if HeaderFlag set else
original value
If InLink.Row = DepartmentRow then InLink.Row Else DepartRow |
DepartmentRow | Save Department row else set to prior
value
If InLink.Row = CenterLine Then InLink.Row Else CenterRow |
CenterRow | Save CenterRow else set to prior
value
If InLink.Row = DataRow Then InLink.Row Else DataRow | DataRow
| Save DataRow Else set to prior value
If Inlink.Row = DataRow Then 1 Else 0
| WriteFlag | used in the output link
constraint for group break
OutputLink Constraint = WriteFlag - this is same as:
If WriteFlag = 1 Then 1 else 0
Stage Variables are extreamly powerful for doing multi-row processing such
as your attempting. If there is no way to determine what a header,
DepartmentRow, or other row is then the other technique is to use a rotating
counter. Where it starts at 1 and increments until the last row of the set
(assumes you have for example 1 through n rows in each set where n is always
fixed) You can increment the counter variable until it maxs out then reset it to one. This assumes that you have a fixed number of rows for each set/grouping. This will always be the first stage variable as the following variables will check to see the value of the variable (i.e. if variable = 1 then header, if variable = 2 then department etc)..
Hope this is helpful.
Regards,
Allen Spayth
----- Original Message -----
From:
To:
Sent: Tuesday, April 24, 2001 5:18 AM
Subject: informix-datastage@oliver.com
> Greetings,
>
> I need to read a sequential file extract from a data system. The
> file contains several lines of indented heirarchy before reaching the
> one or more true data lines. When I hit a data line, I need to create
> an output record with the comma delimited data from the data line plus
> all of the previous heirarchy data. An example
> 1234567 >
> Department line
> abcde > Cost
> center line
> building2 >
> Location line
> Bob,10,20,30,40,.... > Data line
>
>
> Output line
> 1234567,abcde,building2,Bob,10,20,30,40,...
>
> I tried to use stage variables, but they seem to reset prior to
> getting to the data line. Any help would be appreciated. By the way,
> Im using DS 4.0 on a Sun/Solaris server. Thanks, Bob
>
Another suggestion would be to use stage variables (As you tried before). However, do not overwrite the stage variables. I also assume that you are reading the entire row as a single column and parsing it when required.
Derivation
| Variable Name | Description
==========================================================================
If InLink.Row = HeaderRow Then 1 Else 0
| HeaderRowFlag | This is saved for later reference
If HeaderRowFlag Then InLink.Row Else HeaderRow |
HeaderRow | Updated if HeaderFlag set else
original value
If InLink.Row = DepartmentRow then InLink.Row Else DepartRow |
DepartmentRow | Save Department row else set to prior
value
If InLink.Row = CenterLine Then InLink.Row Else CenterRow |
CenterRow | Save CenterRow else set to prior
value
If InLink.Row = DataRow Then InLink.Row Else DataRow | DataRow
| Save DataRow Else set to prior value
If Inlink.Row = DataRow Then 1 Else 0
| WriteFlag | used in the output link
constraint for group break
OutputLink Constraint = WriteFlag - this is same as:
If WriteFlag = 1 Then 1 else 0
Stage Variables are extreamly powerful for doing multi-row processing such
as your attempting. If there is no way to determine what a header,
DepartmentRow, or other row is then the other technique is to use a rotating
counter. Where it starts at 1 and increments until the last row of the set
(assumes you have for example 1 through n rows in each set where n is always
fixed) You can increment the counter variable until it maxs out then reset it to one. This assumes that you have a fixed number of rows for each set/grouping. This will always be the first stage variable as the following variables will check to see the value of the variable (i.e. if variable = 1 then header, if variable = 2 then department etc)..
Hope this is helpful.
Regards,
Allen Spayth
----- Original Message -----
From:
To:
Sent: Tuesday, April 24, 2001 5:18 AM
Subject: informix-datastage@oliver.com
> Greetings,
>
> I need to read a sequential file extract from a data system. The
> file contains several lines of indented heirarchy before reaching the
> one or more true data lines. When I hit a data line, I need to create
> an output record with the comma delimited data from the data line plus
> all of the previous heirarchy data. An example
> 1234567 >
> Department line
> abcde > Cost
> center line
> building2 >
> Location line
> Bob,10,20,30,40,.... > Data line
>
>
> Output line
> 1234567,abcde,building2,Bob,10,20,30,40,...
>
> I tried to use stage variables, but they seem to reset prior to
> getting to the data line. Any help would be appreciated. By the way,
> Im using DS 4.0 on a Sun/Solaris server. Thanks, Bob
>
Yikes this did not display very well so Ill try again ..
Stage variable example
Derivation |
Variable Name | Description
============================================================================
if inlink.row = header row then 1 else 0 | HeaderLineFlag | we
set this so we then we can reuse it.
if headerlineflag Then inlink.row else headerline | HeaderLine |
this assigns headerline only if its a header line
if dataline then 1 else 0 | WriteFlag
| this is use in the output link constraint
*** everything else should be the same..
----- Original Message -----
From: "Allen Spayth - wwc"
To:
Sent: Tuesday, April 24, 2001 3:33 PM
Subject: Re: informix-datastage@oliver.com
> Bob,
>
> Using stage variables should work - however you must ensure that you
> only assign them once (or per grouping).. Also I assume that you are
> reading
in
> the entire row as a single column and parsing it. This is usually
> very effective for complex structures.
>
> Stage variable example
>
> Derivation
> | Variable Name | Description
>
============================================================================
> ===========
> if inlink.row = header row then 1 else 0 |
> HeaderLineFlag | we set this so we then we can reuse it.
> if headerlineflag Then inlink.row else headerline |
> HeaderLine | this assigns headerline only if its a header
line
> if dataline then 1 else 0
> | WriteFlag | this is use in the output link constraint
>
>
> This assumes that we have some way to determine what the header row(s)
> are/is I usually am doing some sort of substring operation
> e.g. if inlink.column[4,2] = 01 then 0 else 1 - this checks for
the
> inputlink.column starting position 4 for 2 bytes for a 01
>
> Output link constraint: WriteFlag
>
> This tests for WriteFlag to be 0 or 1 (when 1 it will output a row) -
this
> is equivilent to: If WriteFlag = 1 Then 1 Else 0
>
>
>
> Hope this helps...
>
> Allen Spayth
>
>
>
> ----- Original Message -----
> From:
> To:
> Sent: Tuesday, April 24, 2001 5:18 AM
> Subject: informix-datastage@oliver.com
>
>
> > Greetings,
> >
> > I need to read a sequential file extract from a data system. The
file
> > contains several lines of indented heirarchy before reaching the one
> > or more true data lines. When I hit a data line, I need to create
> > an
output
> > record with the comma delimited data from the data line plus all of
> > the previous heirarchy data. An example
> > 1234567 >
> > Department line
> > abcde >
Cost
> > center line
> > building2 >
> > Location line
> > Bob,10,20,30,40,.... > Data
line
> >
> >
> > Output line
> > 1234567,abcde,building2,Bob,10,20,30,40,...
> >
> > I tried to use stage variables, but they seem to reset prior to
> > getting
to
> > the data line. Any help would be appreciated. By the way, Im
> > using DS 4.0 on a Sun/Solaris server. Thanks, Bob
> >
>
Stage variable example
Derivation |
Variable Name | Description
============================================================================
if inlink.row = header row then 1 else 0 | HeaderLineFlag | we
set this so we then we can reuse it.
if headerlineflag Then inlink.row else headerline | HeaderLine |
this assigns headerline only if its a header line
if dataline then 1 else 0 | WriteFlag
| this is use in the output link constraint
*** everything else should be the same..
----- Original Message -----
From: "Allen Spayth - wwc"
To:
Sent: Tuesday, April 24, 2001 3:33 PM
Subject: Re: informix-datastage@oliver.com
> Bob,
>
> Using stage variables should work - however you must ensure that you
> only assign them once (or per grouping).. Also I assume that you are
> reading
in
> the entire row as a single column and parsing it. This is usually
> very effective for complex structures.
>
> Stage variable example
>
> Derivation
> | Variable Name | Description
>
============================================================================
> ===========
> if inlink.row = header row then 1 else 0 |
> HeaderLineFlag | we set this so we then we can reuse it.
> if headerlineflag Then inlink.row else headerline |
> HeaderLine | this assigns headerline only if its a header
line
> if dataline then 1 else 0
> | WriteFlag | this is use in the output link constraint
>
>
> This assumes that we have some way to determine what the header row(s)
> are/is I usually am doing some sort of substring operation
> e.g. if inlink.column[4,2] = 01 then 0 else 1 - this checks for
the
> inputlink.column starting position 4 for 2 bytes for a 01
>
> Output link constraint: WriteFlag
>
> This tests for WriteFlag to be 0 or 1 (when 1 it will output a row) -
this
> is equivilent to: If WriteFlag = 1 Then 1 Else 0
>
>
>
> Hope this helps...
>
> Allen Spayth
>
>
>
> ----- Original Message -----
> From:
> To:
> Sent: Tuesday, April 24, 2001 5:18 AM
> Subject: informix-datastage@oliver.com
>
>
> > Greetings,
> >
> > I need to read a sequential file extract from a data system. The
file
> > contains several lines of indented heirarchy before reaching the one
> > or more true data lines. When I hit a data line, I need to create
> > an
output
> > record with the comma delimited data from the data line plus all of
> > the previous heirarchy data. An example
> > 1234567 >
> > Department line
> > abcde >
Cost
> > center line
> > building2 >
> > Location line
> > Bob,10,20,30,40,.... > Data
line
> >
> >
> > Output line
> > 1234567,abcde,building2,Bob,10,20,30,40,...
> >
> > I tried to use stage variables, but they seem to reset prior to
> > getting
to
> > the data line. Any help would be appreciated. By the way, Im
> > using DS 4.0 on a Sun/Solaris server. Thanks, Bob
> >
>
Bob_2,
Sounds like you will need to Normalise your records.
I suspect your record structure looks like this, ,,,,...,
and you need to get to this ...
,
,
,
...
Conceptually what you will need to do is this.
Read in each field and append a CRLF (Char(13):Char(10)) to the end of each logical record.
Eg : : : : : : ... : :
So what you will be doing is generating what appears to be one long string but appending CRLF whenever you need to. In this way, you actually control where the record ends. You can do this using Stage Variables quite easily. If you want the fields comma delimited you will have to manually add a comma in between each field otherwise stick to the fixed width format.
This will obviously have the effect of creating records delimited by a carriage return line feed when you next read the file. Your output file should consist of one field only which is really a concatenation of all the fields with all the CRLFs. Please also note that the output file must have the Delimiter and Quote character set to 000 (Null) in the output file. The next time you read the file you must then cater for the fact that you have all these extra CRLFs. We have used this technique here at Qantas quite successfully.
The other option for you may also be to use the Complex Flat File stage available in DS 4.1.1. Havent gone down this path before so if anyone at Ascential can offer assistance, most appreciated.
Good Luck
Basil Privitera
Altis Consulting
From: on 25/04/2001 18:13 AST
Please respond to informix-datastage@oliver.com
To: informix-datastage@oliver.com
cc:
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
Sounds like you will need to Normalise your records.
I suspect your record structure looks like this, ,,,,...,
and you need to get to this ...
,
,
,
...
Conceptually what you will need to do is this.
Read in each field and append a CRLF (Char(13):Char(10)) to the end of each logical record.
Eg : : : : : : ... : :
So what you will be doing is generating what appears to be one long string but appending CRLF whenever you need to. In this way, you actually control where the record ends. You can do this using Stage Variables quite easily. If you want the fields comma delimited you will have to manually add a comma in between each field otherwise stick to the fixed width format.
This will obviously have the effect of creating records delimited by a carriage return line feed when you next read the file. Your output file should consist of one field only which is really a concatenation of all the fields with all the CRLFs. Please also note that the output file must have the Delimiter and Quote character set to 000 (Null) in the output file. The next time you read the file you must then cater for the fact that you have all these extra CRLFs. We have used this technique here at Qantas quite successfully.
The other option for you may also be to use the Complex Flat File stage available in DS 4.1.1. Havent gone down this path before so if anyone at Ascential can offer assistance, most appreciated.
Good Luck
Basil Privitera
Altis Consulting
From: on 25/04/2001 18:13 AST
Please respond to informix-datastage@oliver.com
To: informix-datastage@oliver.com
cc:
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
Bob_2,
Ray has of course noticed a slight flaw in my posting. You will of course have to use just a Char(13) on your Solaris system as an EOL rather than a
Char(13):Char(10) as used on our Windows NT System here at Qantas. Thanks Ray.
Regards
Basil Privitera
Altis Consulting
From: "Basil BPR05 Privitera" on 26/04/2001
10:22
Please respond to informix-datastage@oliver.com
To: informix-datastage@oliver.com
cc:
Subject: Re: informix-datastage@oliver.com
Bob_2,
Sounds like you will need to Normalise your records.
I suspect your record structure looks like this, ,,,,...,
and you need to get to this ...
,
,
,
...
Conceptually what you will need to do is this.
Read in each field and append a CRLF (Char(13):Char(10)) to the end of each logical record.
Eg : : : : : : ... : :
So what you will be doing is generating what appears to be one long string but appending CRLF whenever you need to. In this way, you actually control where the record ends. You can do this using Stage Variables quite easily. If you want the fields comma delimited you will have to manually add a comma in between each field otherwise stick to the fixed width format.
This will obviously have the effect of creating records delimited by a carriage return line feed when you next read the file. Your output file should consist of one field only which is really a concatenation of all the fields with all the CRLFs. Please also note that the output file must have the Delimiter and Quote character set to 000 (Null) in the output file. The next time you read the file you must then cater for the fact that you have all these extra CRLFs. We have used this technique here at Qantas quite successfully.
The other option for you may also be to use the Complex Flat File stage available in DS 4.1.1. Havent gone down this path before so if anyone at Ascential can offer assistance, most appreciated.
Good Luck
Basil Privitera
Altis Consulting
From: on 25/04/2001 18:13 AST
Please respond to informix-datastage@oliver.com
To: informix-datastage@oliver.com
cc:
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
Ray has of course noticed a slight flaw in my posting. You will of course have to use just a Char(13) on your Solaris system as an EOL rather than a
Char(13):Char(10) as used on our Windows NT System here at Qantas. Thanks Ray.
Regards
Basil Privitera
Altis Consulting
From: "Basil BPR05 Privitera" on 26/04/2001
10:22
Please respond to informix-datastage@oliver.com
To: informix-datastage@oliver.com
cc:
Subject: Re: informix-datastage@oliver.com
Bob_2,
Sounds like you will need to Normalise your records.
I suspect your record structure looks like this, ,,,,...,
and you need to get to this ...
,
,
,
...
Conceptually what you will need to do is this.
Read in each field and append a CRLF (Char(13):Char(10)) to the end of each logical record.
Eg : : : : : : ... : :
So what you will be doing is generating what appears to be one long string but appending CRLF whenever you need to. In this way, you actually control where the record ends. You can do this using Stage Variables quite easily. If you want the fields comma delimited you will have to manually add a comma in between each field otherwise stick to the fixed width format.
This will obviously have the effect of creating records delimited by a carriage return line feed when you next read the file. Your output file should consist of one field only which is really a concatenation of all the fields with all the CRLFs. Please also note that the output file must have the Delimiter and Quote character set to 000 (Null) in the output file. The next time you read the file you must then cater for the fact that you have all these extra CRLFs. We have used this technique here at Qantas quite successfully.
The other option for you may also be to use the Complex Flat File stage available in DS 4.1.1. Havent gone down this path before so if anyone at Ascential can offer assistance, most appreciated.
Good Luck
Basil Privitera
Altis Consulting
From: on 25/04/2001 18:13 AST
Please respond to informix-datastage@oliver.com
To: informix-datastage@oliver.com
cc:
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