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
Create a record if entry for a key field does not exist
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
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....
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....
Julio Rodriguez
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses