Hi All,
I have 2 sequential files each having one set of values. I need to pick up one value from each set and construct a record to be inserted to the database. For example:
File1 File2
----- -----
valuei valuex
valuej valuey
valuek valuez
So my 3 records will be :
1) valuei valuex
2) valuej valuey
3) valuek valuez
Could someone help me out!!!
Thanks in advance..
Nilotpal.
nroy@xavient.com
cc - nilotpalr@mail.com
Multiple source files as inputs
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 94
- Joined: Wed May 08, 2002 8:44 am
- Location: Germany
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
The merge stage will probably not work because you do not have a key that joins rows from the two files. Your join criteria appears to be the row number, ie join row 1 to 1, row 2 to 2 etc. The join criteria in a merge stage is quite restrictive and it will not let you put row number in as a key. It only allows joins of input columns.
One method for joining the files is to put row number into your source files and join on that value. Klaus' second suggestion is a good one. Here is is how a surrogate key can be built:
Extract file 2 into a hash file called Value.hsh containing two columns, the first is a numeric column called RowNum populated with the system variable @INROWNUM the second contains the input data. Make RowNum the key.
Create a transformer with two inputs, one of them is file 1, the other is the hash file containing file 2 with a link called Value_hsh. In the transformer type @INROWNUM into the mapping field for Value_hsh RowNum field. This ensures that input row 2 will map to RowNum 2 etc.
The output will have have two columns, the data from file 1 and the data column from Value.hsh. This should give you three rows outputed.
regards
Vincent
One method for joining the files is to put row number into your source files and join on that value. Klaus' second suggestion is a good one. Here is is how a surrogate key can be built:
Extract file 2 into a hash file called Value.hsh containing two columns, the first is a numeric column called RowNum populated with the system variable @INROWNUM the second contains the input data. Make RowNum the key.
Create a transformer with two inputs, one of them is file 1, the other is the hash file containing file 2 with a link called Value_hsh. In the transformer type @INROWNUM into the mapping field for Value_hsh RowNum field. This ensures that input row 2 will map to RowNum 2 etc.
The output will have have two columns, the data from file 1 and the data column from Value.hsh. This should give you three rows outputed.
regards
Vincent