DSXchange

I have a requirement to make sure there are 3 records for each key fields in an output table regardless of how many come in from the input file.

There are 3 types of records for each account. Types are ABC, DEF, GHI. The Account field is the key field.

Example:

Input File Layout - Account,Type,amount

12345,ABC,1000.25
12345,GHI,2000.15
67890,DEF,100.23

Expected Output:

12345,ABC,1000.25
12345,DEF,0
12345,GHI,2000.15
67890,ABC,0
67890,DEF,100.23
67890,GHI,0

Any suggestions are appreciated. I am still learning datastage and have found this forum to be very helpful.

Thank You

What have you tried? Have you specified the algorithm in a design document of some kind?

the simple 2 cent answer is to sort the data so the records are together, then append a column for which record each is in its group (1,2,3 for example). Then 3 outputs out of a transformer based off the 1,2,3 value, and if the data isnt there, create it for the given path. There are other ways but with only 3 of them, this isnt too difficult.

Read your file, add a transformer with 4 output links
connected to your DB connector
Set the first three links to INSERT NEW RECORDS ONLY, map the account from the file input column and hardcoded the account type for each of your account type "ABC", "DEF" and "GHI" and the amount to zero. This will create records for the missing account type

The 4th link set it to UPDATE and map all the columns from the input file, ensure that this link is the last to trigger in the transformer

You would need to have the account and account type define as key in the DB connector

Let us know how it goes....

This was very helpful. Thank you again.