Hello,
Could someone please advise how I should be tackling the problem below?
I wish to read a file that has many record types in it. In fact, I am only interested in two record types. The file is 232 characters long. A record type is identified by 2 characters. Those ones that I am interested in are H2 and T1. H2 record type is 80 characters long, T1 record type is 232 characters long.
Each H2 record is followed by a number of T1 records. My extraction requires extracting certain fields from the T1 records and then assign a statement date to each T1 record. The statement date is extracted from the H2 record.
How can I achieve this task?
Can I redefine the record structure within the same sequential file based on different record type?
Cheers,
Paul.
Design issue
Define metadata for the input file as having one column. I presume your column is terminated.
Define a stage variable (perhaps called RecordType) in a Transformer stage. Initialize it to anything. As each record is read, update the RecordType stage variable with the first two characters of the input column.
Use the value of the stage variable as constraints on output links. One output link (called T1?) has the expression RecordType = "T1", where RecordType is a stage variable and "T1" is a string.
Stage variables are evaluated after all input links but before any output link.
Derive output columns on these links by substring extraction, delimited substring extraction, or any similar technique.
If rows in the input file are not terminated, define the metadata as fixed width 232 (or more) characters wide, and set the "incomplete column" rules (scroll right in the Sequential File stages Columns grid to find these rules).
Enrol in DS-ADV (Advanced DataStage) to learn these kinds of technique!
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Friday, 07 September 2001 13:53
To: datastage-users@oliver.com
Subject: Design issue
Hello,
Could someone please advise how I should be tackling the problem below?
I wish to read a file that has many record types in it. In fact, I am only interested in two record types. The file is 232 characters long. A record type is identified by 2 characters. Those ones that I am interested in are H2 and T1. H2 record type is 80 characters long, T1 record type is 232 characters long.
Each H2 record is followed by a number of T1 records. My extraction requires extracting certain fields from the T1 records and then assign a statement date to each T1 record. The statement date is extracted from the H2 record.
How can I achieve this task?
Can I redefine the record structure within the same sequential file based on different record type?
Cheers,
Paul.
Define a stage variable (perhaps called RecordType) in a Transformer stage. Initialize it to anything. As each record is read, update the RecordType stage variable with the first two characters of the input column.
Use the value of the stage variable as constraints on output links. One output link (called T1?) has the expression RecordType = "T1", where RecordType is a stage variable and "T1" is a string.
Stage variables are evaluated after all input links but before any output link.
Derive output columns on these links by substring extraction, delimited substring extraction, or any similar technique.
If rows in the input file are not terminated, define the metadata as fixed width 232 (or more) characters wide, and set the "incomplete column" rules (scroll right in the Sequential File stages Columns grid to find these rules).
Enrol in DS-ADV (Advanced DataStage) to learn these kinds of technique!
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Friday, 07 September 2001 13:53
To: datastage-users@oliver.com
Subject: Design issue
Hello,
Could someone please advise how I should be tackling the problem below?
I wish to read a file that has many record types in it. In fact, I am only interested in two record types. The file is 232 characters long. A record type is identified by 2 characters. Those ones that I am interested in are H2 and T1. H2 record type is 80 characters long, T1 record type is 232 characters long.
Each H2 record is followed by a number of T1 records. My extraction requires extracting certain fields from the T1 records and then assign a statement date to each T1 record. The statement date is extracted from the H2 record.
How can I achieve this task?
Can I redefine the record structure within the same sequential file based on different record type?
Cheers,
Paul.
We did similar to what Ray suggested.
However I wrote to separate output links for each input record type. In this way I could then read the data back in via another link which had the matching Meta-Definition for that actual record type. I wore the overhead of writing to an intermediate file (however, I suppose you could use a pipe).
What I found difficult was how I could still have available the data from a previous record (eg. H2) when I was processing another (eg. T1) within the one transform stage. Especially when the H2 record was not the one directly before.
I used a stage variable which stored details from H2. The derivation only stored a new value when the next H2 came along (otherwise it kept storing the same value). I then used this stage variable when processing T1 rows.
Eg.the Derivation for StageVariable1 would be:
If left(Input.data,2) = "H2" then SomeInputVariable else StageVariable1
Don Bradshaw
AM Corporation Limited
mailto: donbradshaw@amcorp.com.au
http://www.amcorp.com.au
-----Original Message-----
From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
Sent: Friday, September 07, 2001 2:09 PM
To: datastage-users@oliver.com
Subject: RE: Design issue
Define metadata for the input file as having one column. I presume your column is terminated.
Define a stage variable (perhaps called RecordType) in a Transformer stage. Initialize it to anything. As each record is read, update the RecordType stage variable with the first two characters of the input column.
Use the value of the stage variable as constraints on output links. One output link (called T1?) has the expression RecordType = "T1", where RecordType is a stage variable and "T1" is a string.
Stage variables are evaluated after all input links but before any output link.
Derive output columns on these links by substring extraction, delimited substring extraction, or any similar technique.
If rows in the input file are not terminated, define the metadata as fixed width 232 (or more) characters wide, and set the "incomplete column" rules (scroll right in the Sequential File stages Columns grid to find these rules).
Enrol in DS-ADV (Advanced DataStage) to learn these kinds of technique!
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Friday, 07 September 2001 13:53
To: datastage-users@oliver.com
Subject: Design issue
Hello,
Could someone please advise how I should be tackling the problem below?
I wish to read a file that has many record types in it. In fact, I am only interested in two record types. The file is 232 characters long. A record type is identified by 2 characters. Those ones that I am interested in are H2 and T1. H2 record type is 80 characters long, T1 record type is 232 characters long.
Each H2 record is followed by a number of T1 records. My extraction requires extracting certain fields from the T1 records and then assign a statement date to each T1 record. The statement date is extracted from the H2 record.
How can I achieve this task?
Can I redefine the record structure within the same sequential file based on different record type?
Cheers,
Paul.
__________________________________________________________________
PRIVATE AND CONFIDENTIAL
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error please disregard the contents of the email, delete the email and notify the author immediately.
However I wrote to separate output links for each input record type. In this way I could then read the data back in via another link which had the matching Meta-Definition for that actual record type. I wore the overhead of writing to an intermediate file (however, I suppose you could use a pipe).
What I found difficult was how I could still have available the data from a previous record (eg. H2) when I was processing another (eg. T1) within the one transform stage. Especially when the H2 record was not the one directly before.
I used a stage variable which stored details from H2. The derivation only stored a new value when the next H2 came along (otherwise it kept storing the same value). I then used this stage variable when processing T1 rows.
Eg.the Derivation for StageVariable1 would be:
If left(Input.data,2) = "H2" then SomeInputVariable else StageVariable1
Don Bradshaw
AM Corporation Limited
mailto: donbradshaw@amcorp.com.au
http://www.amcorp.com.au
-----Original Message-----
From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
Sent: Friday, September 07, 2001 2:09 PM
To: datastage-users@oliver.com
Subject: RE: Design issue
Define metadata for the input file as having one column. I presume your column is terminated.
Define a stage variable (perhaps called RecordType) in a Transformer stage. Initialize it to anything. As each record is read, update the RecordType stage variable with the first two characters of the input column.
Use the value of the stage variable as constraints on output links. One output link (called T1?) has the expression RecordType = "T1", where RecordType is a stage variable and "T1" is a string.
Stage variables are evaluated after all input links but before any output link.
Derive output columns on these links by substring extraction, delimited substring extraction, or any similar technique.
If rows in the input file are not terminated, define the metadata as fixed width 232 (or more) characters wide, and set the "incomplete column" rules (scroll right in the Sequential File stages Columns grid to find these rules).
Enrol in DS-ADV (Advanced DataStage) to learn these kinds of technique!
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Friday, 07 September 2001 13:53
To: datastage-users@oliver.com
Subject: Design issue
Hello,
Could someone please advise how I should be tackling the problem below?
I wish to read a file that has many record types in it. In fact, I am only interested in two record types. The file is 232 characters long. A record type is identified by 2 characters. Those ones that I am interested in are H2 and T1. H2 record type is 80 characters long, T1 record type is 232 characters long.
Each H2 record is followed by a number of T1 records. My extraction requires extracting certain fields from the T1 records and then assign a statement date to each T1 record. The statement date is extracted from the H2 record.
How can I achieve this task?
Can I redefine the record structure within the same sequential file based on different record type?
Cheers,
Paul.
__________________________________________________________________
PRIVATE AND CONFIDENTIAL
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error please disregard the contents of the email, delete the email and notify the author immediately.
This technique, too, (remembering values from previous rows) is taught in DS-ADV !
And you should be able to use neither an intermediate file nor a pipe; just the internal handling of DataStage - at worst an additional Transformer stage.
-----Original Message-----
From: Don Bradshaw [mailto:donbradshaw@amcorp.com.au]
Sent: Friday, 07 September 2001 15:50
To: datastage-users@oliver.com
Subject: RE: Design issue
We did similar to what Ray suggested.
However I wrote to separate output links for each input record type. In this way I could then read the data back in via another link which had the matching Meta-Definition for that actual record type. I wore the overhead of writing to an intermediate file (however, I suppose you could use a pipe).
What I found difficult was how I could still have available the data from a previous record (eg. H2) when I was processing another (eg. T1) within the one transform stage. Especially when the H2 record was not the one directly before.
I used a stage variable which stored details from H2. The derivation only stored a new value when the next H2 came along (otherwise it kept storing the same value). I then used this stage variable when processing T1 rows.
Eg.the Derivation for StageVariable1 would be:
If left(Input.data,2) = "H2" then SomeInputVariable else StageVariable1
Don Bradshaw
AM Corporation Limited
mailto: donbradshaw@amcorp.com.au
http://www.amcorp.com.au
-----Original Message-----
From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
Sent: Friday, September 07, 2001 2:09 PM
To: datastage-users@oliver.com
Subject: RE: Design issue
Define metadata for the input file as having one column. I presume your column is terminated.
Define a stage variable (perhaps called RecordType) in a Transformer stage. Initialize it to anything. As each record is read, update the RecordType stage variable with the first two characters of the input column.
Use the value of the stage variable as constraints on output links. One output link (called T1?) has the expression RecordType = "T1", where RecordType is a stage variable and "T1" is a string.
Stage variables are evaluated after all input links but before any output link.
Derive output columns on these links by substring extraction, delimited substring extraction, or any similar technique.
If rows in the input file are not terminated, define the metadata as fixed width 232 (or more) characters wide, and set the "incomplete column" rules (scroll right in the Sequential File stages Columns grid to find these rules).
Enrol in DS-ADV (Advanced DataStage) to learn these kinds of technique!
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Friday, 07 September 2001 13:53
To: datastage-users@oliver.com
Subject: Design issue
Hello,
Could someone please advise how I should be tackling the problem below?
I wish to read a file that has many record types in it. In fact, I am only interested in two record types. The file is 232 characters long. A record type is identified by 2 characters. Those ones that I am interested in are H2 and T1. H2 record type is 80 characters long, T1 record type is 232 characters long.
Each H2 record is followed by a number of T1 records. My extraction requires extracting certain fields from the T1 records and then assign a statement date to each T1 record. The statement date is extracted from the H2 record.
How can I achieve this task?
Can I redefine the record structure within the same sequential file based on different record type?
Cheers,
Paul.
__________________________________________________________________
PRIVATE AND CONFIDENTIAL
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error please disregard the contents of the email, delete the email and notify the author immediately.
And you should be able to use neither an intermediate file nor a pipe; just the internal handling of DataStage - at worst an additional Transformer stage.
-----Original Message-----
From: Don Bradshaw [mailto:donbradshaw@amcorp.com.au]
Sent: Friday, 07 September 2001 15:50
To: datastage-users@oliver.com
Subject: RE: Design issue
We did similar to what Ray suggested.
However I wrote to separate output links for each input record type. In this way I could then read the data back in via another link which had the matching Meta-Definition for that actual record type. I wore the overhead of writing to an intermediate file (however, I suppose you could use a pipe).
What I found difficult was how I could still have available the data from a previous record (eg. H2) when I was processing another (eg. T1) within the one transform stage. Especially when the H2 record was not the one directly before.
I used a stage variable which stored details from H2. The derivation only stored a new value when the next H2 came along (otherwise it kept storing the same value). I then used this stage variable when processing T1 rows.
Eg.the Derivation for StageVariable1 would be:
If left(Input.data,2) = "H2" then SomeInputVariable else StageVariable1
Don Bradshaw
AM Corporation Limited
mailto: donbradshaw@amcorp.com.au
http://www.amcorp.com.au
-----Original Message-----
From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
Sent: Friday, September 07, 2001 2:09 PM
To: datastage-users@oliver.com
Subject: RE: Design issue
Define metadata for the input file as having one column. I presume your column is terminated.
Define a stage variable (perhaps called RecordType) in a Transformer stage. Initialize it to anything. As each record is read, update the RecordType stage variable with the first two characters of the input column.
Use the value of the stage variable as constraints on output links. One output link (called T1?) has the expression RecordType = "T1", where RecordType is a stage variable and "T1" is a string.
Stage variables are evaluated after all input links but before any output link.
Derive output columns on these links by substring extraction, delimited substring extraction, or any similar technique.
If rows in the input file are not terminated, define the metadata as fixed width 232 (or more) characters wide, and set the "incomplete column" rules (scroll right in the Sequential File stages Columns grid to find these rules).
Enrol in DS-ADV (Advanced DataStage) to learn these kinds of technique!
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Friday, 07 September 2001 13:53
To: datastage-users@oliver.com
Subject: Design issue
Hello,
Could someone please advise how I should be tackling the problem below?
I wish to read a file that has many record types in it. In fact, I am only interested in two record types. The file is 232 characters long. A record type is identified by 2 characters. Those ones that I am interested in are H2 and T1. H2 record type is 80 characters long, T1 record type is 232 characters long.
Each H2 record is followed by a number of T1 records. My extraction requires extracting certain fields from the T1 records and then assign a statement date to each T1 record. The statement date is extracted from the H2 record.
How can I achieve this task?
Can I redefine the record structure within the same sequential file based on different record type?
Cheers,
Paul.
__________________________________________________________________
PRIVATE AND CONFIDENTIAL
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error please disregard the contents of the email, delete the email and notify the author immediately.