Lookup having two records

[nick.bond]

Are there only ever two records in table B ? Or are there a maximum number of these records?

If so you can perform the join as you did, read out into a transformer, and using stage variable to hold the values flatten the structure out, writing the records into a hashed file with contact_id as the key, and reading them out of the hashed file again. This will give you the last record that was written into the hashed file for each contact_id.

In the preceding transformer have something like

svPreviousID = svCurrentID
svCurrentID = input.contact_id
svIsNewId = If svPreviousID = svCurrentID then 0 Else 1
svPhoneType1 = If svIsNewID Then input.Phone_Type Else svPhoneType1
svPhoneType2 = If svIsNewID Then @Null Else input.Phone_Type
svPhoneNum1 = If svIsNewID Then input.Phone_Num Else svPhoneNum1
svPhoneNum1 = If svIsNewID Then @Null Else input.Phone_Num

Etc.

This only works if you know there are only two records for each ID. If there could only be one you will need to increase the logic in the stage variables.

[JoshGeorge]

Do left outer join and bring the data in sorted order (Contact_id). In a transformer use stage variables to find if the current record is a duplicate on the sort key. If duplicate keep appending Phone_Type Phone Number to the stage variable and pass the value to the corresponding field. Then use an aggregator stage and pick the last record.

[pradkumar]

Hi

I went on implemented your logics. Sounds great

But if there are two records in my source, in the hash file too, there are two records written. But when I view the data it is only one record.
So i went on using this file in a lookup and loading data into a target table. In lookup, iam doing on contact_id.But I am getting warning as "Unique constraint violated".

I am unable to get the point, why the intial vertical pivoting job shows the no.of target record=no.of source records, when it is supposed to be half of it.

Could anyone explain this scenario.