many records to single records

[Ramdatastage]

Hi,

My source contains 3 fields and my target contains 6 fields.
Source fields are no, contact type , contact details , where as target fields are no, email, mobile, fax, work phone, home phone.
Now i have to populate the data in such a way that if contact Type is E then Email and M is mobile no........
source data is like this
No Contact Type contact details
111 E rrrrr@rrrr.com
222 w 00 2222 3333
111 H 01 1111 1111
111 f 11 1222 2232
222 E asdasd@asda.com

My target should be
no e-mail fax Home work mobile
111 rrrrr@rrrr.com 01 1111 1111 11 1222 2232
222 asdas@asda.com 00 2222 3333

Please suggest me a way to do this.

thanks in advance

Ram.

[rasi]

There are many ways to do it to make a easy start create hash file for each type with the key and the value. In your case you will be creating hash file for WorkNo, EmailID, Fax etc.
After the hash file is created then use those with the hash file already created to populate the necessary fields.

You can sort your source file and use the stage variable to store the value for the mobile, email and fax. Do a check if the key changes if so send the record to the target will all the values from the stage variable.


Regards

[badri]

Hi,

You can use stage variables for arrieving this logic

svCntType1 => If LNK_Source.ContactType = 'E' Then LNK_Source.ContactDetails Else ''

svCntType2 => If LNK_Source.ContactType = 'M' Then LNK_Source.ContactDetails Else ''

Place the svCntType1 against the EMail field (target) and svCntType2 against the Mobile field (target)

Hope this helps..

[rasi]

Badri

By doing in your way won't merge records into single record. To work out your logic you need to sort the source records based on the key and then use the Stage Variables to store all the types and do the check based on the key to find the next record is different to pass it to the target.

Cheers

[vmcburney]

Can I suggest that you send the data to a transformer, map your simple input stream to your wide output stream where email, phone number etc are all output fields. Based on the contact type you map your value field to an output field. Several of the fields will be blank for each record. Send the output to an aggregation stage where you group by No and take the maximum value for all other fields.

This will produce a single output stream where all fields have been brought together.

I wouldn't recommend stage variables as your data isn't sorted.

[olgc]

For the sort & variable way, you guys will lose the last record. So only hashed file works.

Thanks,

[ray.wurlod]

For the sort & variable way you can run ExecSH (or ExecDOS) as a before-job subroutine that appends a dummy row to the source data.

Code: Select all

echo "<NULL>,<NULL>,<NULL> " >> #inputfile#

[vmcburney]

When you send the data through a sort stage will the dummy row go to the start or the end of the data stream?

[vmcburney]

For the sort & variable way, you guys will lose the last record. So only hashed file works.

This is a basic vertical pivot and all three methods mentioned will work:
- Dummy Row/Sort/Stage variables requires a mix of simple Unix commands and potentially difficult stage variable coding but should provide the fastest performance. I use this approach on high volumes.
- Hash file lookup requires the population of a temporary hash file and multiple lookups from this file. It means processing your source file twice and building a job with twice as many stages as the other approaches. I only use this approach when the hash file can be re-used by other jobs.
- Aggregation approach requires just four stages and is the simplest design but the aggregation of non sorted data can be time consuming. I use this approach for moderate data volumes as it is the easiest to build and maintain.