Multiple source files as inputs

[nilotpalr]

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

[Klaus Schaefer]

You may probably use the MERGE stage to do this. This is the only way to merge 2 files for input and continue with one output stream.

Another possible option would be to read one of the files with a surrogate key into a hash file and then have it in a transformer as a regular lookup.

Klaus

[vmcburney]

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