Query regarding hash file

[G SHIVARANJANI]

Hi,

I have a requirment where in

I need to match records from two different sources (1.table,2.sequential file->reference). The matching criteria is based on columns which are nullable and not unique.so there is no column which can be made as a key for moving the records through hash file.

Condition for match :
[source 1 has col1,col2,col3,col4 and source 2 has col1,col2,col3
i need to move the records to table 1 when source1.col1=source2.col1 or
source1.col2=source2.col2 or source1.col3=source2.col3 gets satisfied else i need to move to another table2.]

How to move all the records coming from seq.file to hash file without missing any record.


Please suggest.

[DeepakCorning]

Store the reference in three different hashed files. Hashed file 1 - Col1 as key , Hashed file 2 - Col 2 as a key and so on.

Compare the first col1 (hashed file1) in the first stage and store the result in a stage variable , pass it on to the next stage and then compare the col2 (hashed file2) and so on.

Combine the result in the end stage and see if atleast one compare was ok and if yes then pass it on to the trgt.

You actually so all this in one stage as well but if u divide it then it will be easy for debugging and all.

:D

[G SHIVARANJANI]

But i also need the records which are not satisfying any of the three cases, both from lookup as well as from reference and i need those records to be moved to table2

Store the reference in three different hashed files. Hashed file 1 - Col1 as key , Hashed file 2 - Col 2 as a key and so on.

Compare the first col1 (hashed file1) in the first stage and store the result in a stage variable , pass it on to the next stage and then compare the col2 (hashed file2) and so on.

Combine the result in the end stage and see if atleast one compare was ok and if yes then pass it on to the trgt.

You actually so all this in one stage as well but if u divide it then it will be easy for debugging and all.

:D

[Raghavendra]

As DeepakCorning Said:

Write your source2 in 3 different hashfiles say hashfile file1 with col1 as key, hashfile2 with col2 as key and hashfile 3 with col3 as key.

Compare col1 from source1 with hashfile1,col2 from source1 with col2 from hashfile2 and col3 from source1 with col3 from hashfile3
Set 3 flags for these 3 comparisions.For example if the lookup satisfied set the flag as 'Y' else 'N'.

If any flag is set to 'Y' then move to table 1 else to table2.

You can do this comparision and flag setting in one single transformer only.