Create a record if entry for a key field does not exist

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
elinzeyjr
Participant
Posts: 9
Joined: Thu May 11, 2017 9:18 am

Create a record if entry for a key field does not exist

Post by elinzeyjr »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

What have you tried? Have you specified the algorithm in a design document of some kind?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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.
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

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....
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
elinzeyjr
Participant
Posts: 9
Joined: Thu May 11, 2017 9:18 am

Post by elinzeyjr »

This was very helpful. Thank you again.
Post Reply