DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
elinzeyjr
Participant



Joined: 11 May 2017
Posts: 9

Points: 162

Post Posted: Sat Aug 12, 2017 7:27 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54006
Location: Sydney, Australia
Points: 293010

Post Posted: Sun Aug 13, 2017 11:08 pm Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 261

Points: 2643

Post Posted: Mon Aug 14, 2017 8:25 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 399
Location: New York City
Points: 4365

Post Posted: Tue Aug 15, 2017 6:49 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
elinzeyjr
Participant



Joined: 11 May 2017
Posts: 9

Points: 162

Post Posted: Tue Aug 22, 2017 2:45 pm Reply with quote    Back to top    

This was very helpful. Thank you again.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours